Forum Discussion
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. Somebody any suggestions how to approach (especially the reference to the previous value and the way you should iterate the table (with map or a combination byrow / bycol).
thanks in advance,
Michiel
=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.
- djclementsBronze Contributor
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...
- PeterBartholomew1Silver Contributor
My preferred approach was to offset the data and subtract before unpivoting.
= LET( offsetData, HSTACK(TAKE(data, , 1), DROP(data, , -1)), difference, data - offsetData, broadcastDate, IF(ISNUMBER(data), date), rowHeader, IF(ISNUMBER(data), headings), HSTACK(TOCOL(rowHeader), TOCOL(broadcastDate), TOCOL(difference)) )
- MichielS340Copper Contributor
PeterBartholomew1 very elegant solution 🙇:male_sign:
- OliverScheurichGold Contributor
=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.
- MichielS115Copper ContributorNice solution with good outcomes. thans a lot
I need a bit of time to fully understand the logic behind it 😅