Forum Discussion
Solved
Excel iterate over table and list output with spilling formula
Hi, i am looking for a formula that iterates byrow and presents the differences between the months like the output below. I am not fully sure how to tackle the problem with a spilling formula. Somebo...
=DROP(REDUCE("",SEQUENCE(ROWS(B2:L8)),
LAMBDA(u,v,
VSTACK(u,
TRANSPOSE(
VSTACK(
IFNA(EXPAND(INDEX(B2:B8,v),,10),INDEX(B2:B8,v)),
C1:L1,
HSTACK(
"",
DROP(
MAP(DROP(CHOOSEROWS(C2:L8,v),,1),CHOOSEROWS(C2:L8,v),
LAMBDA(aa,bb,aa-bb)),,-1)
))))))
,1)
This formula returns the intended result in my sample file if i correctly understand what should be done.
MichielS115 An alternative lambda-free method (non-iterative) could be:
=LET(
table; A1:O7;
col_labels; DROP(TAKE(table; 1);; 1);
row_labels; TAKE(DROP(table; 1);; 1);
values; DROP(table; 1; 1);
net_change; values - HSTACK(TAKE(values;; 1); DROP(values;; -1));
VSTACK(
HSTACK("Item"; "Date"; "Net Change");
HSTACK(
TOCOL(IF(SEQUENCE(; COLUMNS(values)); row_labels));
TOCOL(IF(SEQUENCE(ROWS(values)); col_labels));
TOCOL(net_change)
)
)
)
Adjust the table range reference as needed.
Also, if you prefer blanks instead of zeros for the first month, adjust the net_change variable as follows:
=LET(
...
net_change; IFNA(HSTACK(""; DROP(values;; 1) - DROP(values;; -1)); "");
...
)
See attached...