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 just reinput your formula and get the correct result.

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

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

CumulativeFLPRecovery)

Answer 3

Thanks for looking into this Snow.

It does take a few attempts to see the error. Here'sa short video of me typing in data and seeing the errors as they happen.

Since my first post I have gone so far as to do an online repair of "Microsoft 365 Apps for enterprise - en-us" but this recording was done after that. Launching Excel in safe mode also didn't help.

I'm about to commandeer someone else's machine to test this - I'll let you know how that goes.

Thanks again.

Answer 4

BTW, I tried repairing these .xlsx files using the 'Open and Repair..." option in the Open dialog and it turns out that every file gets this error - even a brand new empty Book1.xlsx file that I've saved to my OneDrive. Is this expected? Even if I repair the file and overwrite, I can open it again with the repair option and it reports the same error message.
Is it expected to always get some error when using the Open and Repair... option?

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error228320_01.xml</logFileName><summary>Errors were detected in file 'https://agilent-my.sharepoint.com/personal/jeffrey_knight_agilent_com/Documents/Desktop/FracMacTestBed.xlsx'</summary> <additionalInfo><info>Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.</info></additionalInfo> </recoveryLog>

Answer 5

Issue seems to be a bug when you combine let with sort function.

You may raise it to Microsoft.

Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. That support team there will have the correct channel and resources to help you investigate more and find what exactly the reason has caused this situation.

Get support - Microsoft 365 admin | Microsoft Learn

For the second question, it is not an expected behavior. Have you tried open your file on another machine?