A simple formula trick to visualize your errors
November 3, 2008 • JP • No Comments • Rate This Article
• Links to this article
Here's a neat trick I use to make spotting worksheet errors very simple.
Wrap your formula in an IF statement that returns the letter "J" if True, or the letter "L" if False, and change the cell's font to Wingdings.
In the Wingdings font, "J" is a smiley face 
and "L" is a frown 
. So you (or your end users) can get an immediate visual clue as to the success or failure of their formula. It's low-tech but very effective.
For example, don't just write
=A1=A2
to compare two cells (which just returns a boring TRUE or FALSE to the target cell). Instead, use
=IF(A1=A2,"J","L")
Don't forget to change the cell's font to Wingdings. Even better, blow it up to a size 20 and make it bold to really stand out. 
Let's take it even further with conditional formatting. Set up your target cell's conditional formatting as follows:
Now your smiley face is a happy green color, and the bad formulas get an ugly red color. Perfect for those PHBs who can't be bothered to read a spreadsheet, so they can get a color-coded view. I hope that doesn't dissuade you from using this technique in your own work.
Here's a related topic for which you can use similar techniques: Creating a non-graphic chart directly in a range
Now if I want a count of formula errors, I just use the COUNTIF formula as follows. Assuming I filled down my IF formula down an unspecified number of rows in column B,
="Raw errors: "&COUNTIF(B:B,"L")
would return the number of sad faces (i.e. FALSE returns) in that column.
Typically, though, I'll use the IF formula to evaluate multiple cells in the same row. In that case, I might want to know how many full row errors there are; in other words, how many rows had all of their formulas return FALSE. I'll use SUMPRODUCT for this.
="Full row errors: "&SUMPRODUCT((B2:B5000="L")*(C2:C5000="L"))
or
="Full row errors: "&SUMPRODUCT(–(B2:B5000="L")*–(C2:C5000="L"))
or
="Full row errors: "&SUMPRODUCT(N(B2:B5000="L")*(N(C2:C5000="L")))
These formulas will compare each pair of cells (B2 & C2, B3 & C3, etc), evaluate whether each one equals "L" (1 for TRUE, 0 for FALSE) and multiplies the values together. Only the rows where both expressions evaluate to TRUE will add 1 to the total.
For SUMPRODUCT you can't use full column references (in Excel 2003), so I just chose an arbitrarily large range. Adjust as needed. The only difference with the last two formulas is the double unary (–, that's two hyphens) or N() function, which might be required to convert text to values, but for the most part only useful to achieve "Excel-god" status in your office. 
Enjoy,
JP
Excel ·
Rate This Article:
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Using Excel VBA to set up Task Reminders in Outlook
Next Post: Shipment Tracking Add-In for Excel
Speak Your Mind
Tell us what you're thinking...Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].