Search Not Just Numbers

Wednesday, 27 September 2017

Excel Tip: How to evaluate individual parts of a large formula

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".

4 comments:

  1. yup, this is soooooo useful!
    one of the most useful-yet-little-known methods IMO

    Can also be used to replace externally-linked bits of a formula with their values (NB press Return this time)
    or to replace charted ranges with values (select a charted series, click the formula bar and press F9, Return) - you can then copy the chart to another workbook without it linking back

    jim

    ReplyDelete
    Replies
    1. NB to clarify; you need to select EACH series and F9 separately to unlink the whole chart

      and if there are too many data points then it doesn't work

      jim again

      Delete
    2. Thanks Jim. That's useful. I'd never tried it with charts!

      Delete
  2. This is another great tip, thank you so much!

    ReplyDelete