CEO needaspreadsheet.com
Columnist
Share this content
Tags:

Excel Tip: How to evaluate parts of a formula

27th Sep 2017
CEO needaspreadsheet.com
Columnist
Share this content

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

Tags:

Replies (5)

Please login or register to join the discussion.

avatar
By Henry Crawford
17th Oct 2017 11:22

Nice excel tip! Now I will use this whenever possible, because sometimes there were such problems with the tasks that I perform on buyessay.org/college-essay.html, but I didn't use this formula. Often I have to deal with a large amount of data and any new tips that I didn't know before can be useful at any time. Thanks for sharing!

Thanks (0)
avatar
By Regina2012
21st Aug 2018 09:18

You don't even imagine it is the right time when I found your site. I don't know much about formulas. I am a freelance writer at https://writer-elite.com and just got a task on a related topic. I didn't know how can I fulfill it in time. But with your tips, it makes things easier. Thank you very much!

Thanks (0)
avatar
By Marry2228
26th Sep 2018 08:27

It is a very informative post. I use this advice when I prefer for my lessons. But my brother buy online essay and you don't need this information because he does it faster and have free time for computers games.

Thanks (0)
avatar
By Emma Warter
26th Sep 2018 09:30

Oh, this tip are so useful. I think, I will use it very often. I like to found different helpful information. My favourute the Internet resourse is http://buy-essays-now.net.

Thanks (0)
avatar
By Emma Warter
26th Sep 2018 09:30

Oh, this tip are so useful. I think, I will use it very often. I like to found different helpful information. My favourIte the Internet resourse is http://buy-essays-now.net.

Thanks (0)