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.
=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.
Nice solution with good outcomes. thans a lot
I need a bit of time to fully understand the logic behind it 😅
I need a bit of time to fully understand the logic behind it 😅