Re:The first rule of Microsoft Excel: Don’t tell anyone you’...
by giaour on Monday October 24, 2022 @12:46PM
(#33319072)
Totally agree. This is exactly what I would do to debug a complex expression in a program: break out chunks into smaller expressions, assign their results to intermediate variables, then echo out or inspect those variables in a debugger. Once it clicks with users that an Excel cell is a variable, they tend to write more state out to the sheet so they can inspect it.
Long/complex Excel formulae should usually be broken into smaller chunks, with intermediate results stored in separate cells. For example, if you're calculating two numbers, and then calculating their ratio, it would be better to use three formulae instead of one. That way:
1. Each formula is shorter.
2. Each formula has a single purpose which can be understood.
3. The outputs of the intermediate steps in the calculation are obvious.
This applies also to complex nested IF() statements. Instead of calculating all the conditions inside the IF(), calculate them outside, and then reference the TRUE/FALSE cell values in your IF statement. When you need to debug why you're not getting the result you want, you can easily look at the intermediate calculations all at once, without needing to step through the calculation and check it in the order of calculation.