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".
yup, this is soooooo useful!
ReplyDeleteone 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
NB to clarify; you need to select EACH series and F9 separately to unlink the whole chart
Deleteand if there are too many data points then it doesn't work
jim again
Thanks Jim. That's useful. I'd never tried it with charts!
DeleteFurther to this:
DeleteWhen 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?)
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!
DeleteThis is another great tip, thank you so much!
ReplyDeleteHey 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.
ReplyDeleteuse named ranges and line feeds in formulae to make them more readable
Deleteand try to avoid nesting IFs (you can, but it's not very elegant)
jim
Thanks Jim - good advice!
Delete