Best Microsoft Excel Bloggers

Thursday, October 14, 2010

Evaluating Formulas

In the past, whenever I have talked about debugging or evaluating a formula, I have always talked about the Evaluate Formula icon found on the Formula ribbon
It is very useful as it allows you to break apart complex formulas step by step. Click Evaluate to examine the value of the reference that is underlined. The result of that evaluation is shown in italics.

If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.  And then just keep going until you have worked your way through the entire formula.

If it is a long complex formula, I still think this is the best method but I learned a quicker method the other day on Chandoo's site and I think it is really useful if you are in a hurry or the formula is relatively short.
( If it is a long complex formula, I think it is easier to see the breakdown in the Evaluate Formula dialog box. - just my personal preference.)

 All you need to do is double-click on the formula, select the component that you want to evaluate and press F9. If you have additional formulas, select them and press F9 again. When you are done, press ESC to get out.

Two ways to do the same thing... and yes, if we look around, we will probably find a 3rd method too. Have a good day.

No comments:

Post a Comment

Ms. Excel- Resident Excel Geek