Forum Discussion
Help on Error message "There's a problem with this formula"
If without exotic and use the same formula
=LET( months, $BK$1:$BS$1, years, $BE$1:$BI$1, data, $BK$3:$BS$6, n, SEQUENCE( ROWS(data) ), sums, LAMBDA(v, MMULT(v, --(TRANSPOSE(YEAR(months)) = years) ) ), raw, REDUCE(0, n, LAMBDA(a,i, VSTACK(a, sums(CHOOSEROWS(data,i))) ) ), DROP(raw,1) )
Thanks, it works.
Can you make it more dynamic by spilling into the highlighted rows, not only one row? I knew I could copy down the formula, can it be spilled by one formula to all rows? Thanks, 🙂
If without exotic and use the same formula
=LET(
months, $BK$1:$BS$1,
years, $BE$1:$BI$1,
data, $BK$3:$BS$6,
n, SEQUENCE( ROWS(data) ),
sums, LAMBDA(v, MMULT(v, --(TRANSPOSE(YEAR(months)) = years) ) ),
raw, REDUCE(0, n, LAMBDA(a,i, VSTACK(a, sums(CHOOSEROWS(data,i))) ) ),
DROP(raw,1)
)Wow, you are truly amazing! I knew Reduce function is powerful. My excel level is not high enough to use it like your fluency. This is my second time encountering this Reduce function. I am sure with my consistent effort, I will learn it to be some type of fluency, hopefully. 😊 It took me some time to understand,follow and learn your logic in that outstanding formulas. Thanks, appreciated!
Now I understand the logic behind this formula, however, I have experienced an error message. Would you mind helping me again? What have I done wrong? 😥
That's since we have blank cells within the range. To force converting of blank to zero in operation we may simply use MMULT(v*1, ... ) instead of MMULT(v, ... ). Finally
=LET( months, A1:X1, years, A11#, data, A2:X8, n, SEQUENCE( ROWS(data) ), MntYr, --(TRANSPOSE(YEAR(months))=years), sums, LAMBDA(v, MMULT(v*1, MntYr ) ), raw, REDUCE(0,n,LAMBDA(a,i, VSTACK(a,sums(CHOOSEROWS(data,i))))), DROP(raw,1) )