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.
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))
)PeterBartholomew1 very elegant solution 🙇:male_sign:
