I'm getting wrong answers using SCAN within a LET function (running total)

Question

I use a cumulative sum (ie, running total) function in one of my spreadsheet apps. When I refactored over to the new LET, SCAN, and LAMBA functions I get random answers about half the time. I built a test-bed so that you can see for yourself. Change some of the input data in the yellow cells and you'll occasionally see the Pool recovery function fail. (Oddly, the Pool purity function works fine here, but fails similarly in my actual app.)

Here's an image of what's happening. The upper result table uses older functions that work well, but the lower result table uses newer functions and you can see that the Pool recovery field is not being calculated the same way. What's really odd is that this doesn't happen all the time. You can change the source data (yellow cells) and everything might calculate fine or it might not. One way to always get it to work is to 're-enter' the formula (F2, then Enter) and then it always works, but try changing the source data and it might not.

Here's the Pool recovery function that's failing above:

=LET( SortedFLPRecovery, SORTBY( Table1[Recovery], Table1[Purity], -1 ),

       CumulativeFLPRecovery, SCAN( 0, SortedFLPRecovery, LAMBDA(a,b, a+b ) ), 

       CumulativeFLPRecovery) 

Is there a circular reference in here perhaps?

Jeff Knight

Answer 1

Have you tried adding some helper column to get result? It could be the logical bug in formula calculation.

Answer 2

I sent this problem to my company's IT group and they've closed the issue - not sure if they'll contact you or not, but anyway....

The problem persists even if I remove the LET function.

"=SCAN(0, SORTBY( tblFxnData[FLP Mass], tblFxnData[Purity], -1 ), LAMBDA(a,b, a+b ) ) / SCAN( 0, SORTBY( tblFxnData[Total Mass], tblFxnData[Purity], -1 ), LAMBDA(a,b, a+b) )"

and

"=SCAN( 0, SORTBY( tblFxnData[FLP Recovery], tblFxnData[Purity], -1 ), LAMBDA(a,b, a+b ) ) * ( dn_NormalizedRecoveryTarget / MAX( SCAN( 0, SORTBY( tblFxnData[FLP Recovery], tblFxnData[Purity], -1 ), LAMBDA(a,b, a+b ) ) ) )"

are giving the same problem as before when I used LET()

Answer 3

I agree - there's something wrong with the order of execution, IMO, when combining the entire function into one cell. Breaking the functions out into their own columns has solved the problem. We can close this thread then. Thanks for your help.