Nifty way to troubleshoot formulas

If you’re an experienced Excel use, it’s a sure bet that you’ve found yourself on more than one occasion with long, unwieldy formulas.  While these long, often nested, functions are a necessity when building complex spreadsheets, they can be a huge headache.  Luckily, Microsoft has created a way to help.

While editing a formula, using the F9 key will reveal the result of any selected portion.  Doing this allows you to troubleshoot and debug any formulas that don’t seem to be working correctly.

Let’s say, for example, that you are in charge of a fundraiser at your child’s school.  As a way to make it fun, you’ve decided to add a little incentive to the contest.  The kids tell you they want to see who can sell more candy bars, the boys or the girls.  Simple enough.  To track it, you make a basic spreadsheet with all of the fundraising participants.  (See attached: InCellDebugging)  Along with the name of each student, you add two additional columns for gender and total candy bars sold.  Because the boys outnumber the girls, you decide that the only fair way to measure sales would be to use the average sold per student figure.  Again, simple enough.  The formula you use to do this (for the girls) is:

=SUMIF(C3:C22,”F”,D3:D22)/COUNTIF(C3:C22,”F”)

where:

1. C3:C22 is the gender column
2. F denotes “female”

So while formulas have never been nothing to be afraid of, the F9 key and its usage to dissect these monsters makes them even more harmless.  Have fun, be creative, and above all else, be more efficient and effective!

Note: Using the AverageIf Function does the same thing as dividing Countif by Sumif.  We used the longer method to illustrate the usage of the F9 key to debug formulas.