Error Zen
Learn how to handle Excel errors like a pro using IF, ISNA, and named ranges. A quick guide to cleaner sheets and smarter formulas.
Excel errors (like weeds in a garden) can be useful as indicators. Using IFERROR can mask most ugly horrors preceded by a Hash character, if it is passed a blank string [“”] as the If_True parameter.
At times, however, we need to know exactly what is going wrong. There are a few functions that are useful for this, prefixed with “IS”. The error type can be ascertained combining IF with a test for: ISBLANK, ISERR, , ISERROR, ISEVEN, ISNA, ISODD and so on (there are more).
The example uses an error function, ISNA, (“name not found”) to apply a formula if it finds a column header, but display nothing if not.
The formula used here is =IF(ISNA(MATCH(B$1,Mths,0)),””,B13*$A$13)
Mths is a range, named in the Name Manager only. It is nowhere on a sheet. It was created by invoking Name Manager, clicking New, and typing quoted abbreviated months in an array within curly braces, in the Refers To window:
{“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”}
Excel will prefix this with an equal sign when saved.
The formula looks for a name error (ISNA) MATCH of the column header with named range Mths. If there is an error, meaning that no match is found of the month name, the cell remains blank. If there is a match, the formula applies.
Applied to a whole column in a table, you can cut out certain months, just by changing the month to a number. In the example, a Data Validation is used to supply either month name or its relative number in the headers. When month number is displayed, the formula does not apply, and is not included in YTD sum.
As Miles Davis once said: “It’s not what you play that counts. It’s what you don’t play”.
He surely knew about Error Zen!
Bio: This article was written by Jasper Cook, Excel guru and trombonist of note. Call or email us if KRS and Jasper can assist you with your Excel: enquiries@krs.co.za
Looking for a software partner?
Tell us about your project and we’ll show you how our team can help.
Found this useful? Share it.
