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

9 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
    3. Further to this:
      When you click into a function on the formula bar, Excel offers you the helper text (I'm sure it has a better name) which you can click to highlight individual evaluable parts of your expression
      Easier than trying to precisely drag to select in the formula itself

      jim, yet again (and what's happened to Glen?)

      Delete
    4. Thanks again Jim! I'm still here - just a combination of workload and lack of discipline has stemmed the flow of new posts. Hoping to get things going again soon!

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

    ReplyDelete
  3. Hey I never knew this! Rather like how you can use shift + F9 when debugging VBA macros or .Net programming in Visual Studio to peek at the value of an expression. Thanks. I try and avoid complicated formulas because they are so damn difficult to read. But sometimes it is a trade off between a lots of confusing columns and sheets or complicated formulas. Now at least I have way (and my customers too) of evaluating quickly what each bit of a formula is doing. So thanks again for the tip off.

    ReplyDelete
    Replies
    1. use named ranges and line feeds in formulae to make them more readable
      and try to avoid nesting IFs (you can, but it's not very elegant)

      jim

      Delete