Love them or hate them, Excel formulas can both empower and belittle you as a user greatly.
 |
| “Yes, makes sense. I feel empowered“ |
Learning to use once unfamiliar formulas can lead to the use of more unfamiliar formulas and so on however, any formula that has stages of calculations can remain unfamiliar to you if it is not always obvious how the result is being calculated.
 |
| “WTF?!“ |
Excel does offer the ability to Evaluate Formula that takes you, step-by-step, through every calculation performed by Excel so that you’re able to see how the result is ultimately calculated.
|
| “What you say makes sense, but I’m still not sure“ |
Clicking Evaulate from the Evaluate Formula window will calculate the underscored calculation step in the Evaluate Formula window.
|
| “…I think I see where this is going, but proceed“ |
|
| “YES! Well, I mean it was obvious really,“ |
Whilst it is possible to create a complex formula by having the result at each step calculated in different cells, hence avoiding the need for nesting, nesting remains unavoidable for formulas that will return a range of cells (e.g. OFFSET, INDEX, etc.). Using the Evaluate Formula function becomes very useful.
The Microsoft Office help article can be found here.
