A new way to debug formulas from Excel Labs

Great addition! First I see when started with debugger is

The formula uses features which the debugger does not yet implement.

but need to play more.

m_tarler Yes, the cases from lori_m haven't been fixed yet. They are actually some very hard cases in the formula evaluation engine; the existing (old) debugger in Excel gets these wrong too (when you click "Evaluate formula" under the "Formula Auditing" section of the "Formulas" ribbon tab - which pops up a new dialogue box). They exercise some complex cases in the formula engine, and matching that in the debugger is a long-term task, rather than a short-term fix.

Thank you for your report about PI() though, that is something that I hadn't noticed before, and it will be fixed in a future release. Also, we've taken note of your comments about the "Unsaved edits" banner and will consider some unintrusive changes in that area.

SergeiBaklan I also saw that error when looking at a lambda function; it used arrays which might be the reason. It was working ok with basic formulas

In theory this is HUGE and will help a ton.  The interface seems to have a lot of features (colors, formatting, etc...) to help the user understand but at least for me, I will need some time to get used to it.  It appears any reference to a spilled range isn't supported yet (i.e. A1#)

NOTE: I think I found a slight bug.  I'm guessing it is due using an HTML type interface but TEXT with multiple spaces is not showing the multiple spaces.  Specifically  REPT(" ", 10)  resulted in  text: “ ” . that said the formula still recognizes that there are 10 spaces and MID(REPT(" ", 10) & "ABCDEFGHIJKL" , 9, 1) still returns " ".  It is just a display issue.    

Thank you for the feedback.

SergeiBaklan Yes, we do not have full function coverage in the debugger. There will be more support coming, but in general, as this is a Labs feature we wont get to 100%.

tamtark I hope we can get dynamic array support! The " " issue you saw is a bug with the rendering and we are working on a fix.

Sameer_Bhide We do not have the numbers defined anywhere officially, but we may change it over time. We should have support for FILTER soon.

jflynnz Because we are running as an add-in, we limit the number of cells we read from Excel for performance. The spill reference is probably causing this. I appreciate this is frustrating. We'll think about changing the restrictions (or maybe allowing users to change), but we have to be mindful about resources and keeping in mind this is experimental.

lori_m Thanks for finding this! We looked into this a little more. but I cannot promise a fix at this time.

Thanks again for all the feedback.

SergeiBaklan We have implemented XLOOKUP now.

mtarler Thank you for reporting this issue with space characters; they are now rendered more clearly in the debugger.

There are now 20 more functions in the debugger, and the limits on the number of cells have been boosted.

Thank you all for your feedback.

AlexC84 , lambdas and even direct recursion work. XLOOKUP is not supported, at least in some variants.

jack-williams 

This is a great start.

I just wrote a formula = FILTER(D, D[Product]="ABC","-") - It errors out saying too many cells, then I reduce the table size and then it errors out saying the FILTER function is not supported

Are these limits (number of cells, list of unsupported functions) defined somewhere?

Cheers

Sam

This is a great start.

Interesting, really great work/tool here!

Trying to dig into using it more, and noticing that many of my "formula steps" pane shows an error:

Maximum number of cells in debugger exceeded.

I'm guessing this is due to using dynamic named ranges/spill ranges?

For instance:

=SORTBY(
    FILTER(
        'Line Info Entry'!A15#,
        Data_StructureOrder <> ""
    ),
    Data_StructureOrder
)