From the Not Just Numbers blog:

Just a short but very useful tip this month. But before we start, I’d like to thank those readers who donated to Macmillan Cancer Support when I ran the Great North Run earlier this month. All donations are much appreciated and the page is still open for a little while longer if anyone would still like to donate.

Have you ever written a long formula that isn’t giving the result you’d expect (or is returning an error), but you don’t know which part is the problem? If you’ve ever written a long formula, then it’s a fair bet that you have!

Well, there’s an easy little trick that you might not be aware of, that can make this much easier to investigate.

The problem:
Say you have a formula such as:

=IFERROR(IF(A2>3,1,0),0)

It is returning 0 and you don’t think it should be. This could be because A2 is 3 or less, or because the IF function is returning an error and you don’t know which.

The solution:
You can evaluate any expression within the formula individually, using the F9 key.

Within the cell or formula bar, highlight the section of the formula that you want to evaluate. In this case it would be useful to see whether the output of the IF function alone is a zero or an error, so highlight the IF expression:

=IFERROR(IF(A2>3,1,0),0)

and press the F9 key. The formula will now show the result of the selected expression within the full formula, rather than the expression itself, e.g.

=IFERROR(0,0)

or maybe

=IFERROR(#N/A,0)

You can do this with as many expressions within the formula as you want, as long as you highlight an expression that on its own would return a result.

In this formula you could have alternatively evaluated A2 or A2>3.

IMPORTANT NOTE: When you have finished doing this, leave the cell by pressing Esc rather Enter so that you do not overwrite the expressions with the results.

This is a simple formula for illustrative purposes, but if you have a really long complex formula, this tool can be invaluable.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies “The 5 Excel features that you NEED to know” and “30 Chants for Better Charts”.