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”
If something looks weird, or if you just want to confirm that the formula is working correctly, select the cell that contains the formula.  Then, by clicking and editing inside the formula bar, select the first part of the formula, SUMIF(C3:C22,”F”,D3:D22).  Now, press the F9 key to see the result of that part of the formula.  Pretty cool, huh?  Hitting the key will permanently replace the function or portion with its result, so you might want to hit the key to exit edit mode.

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.

Advertisements

About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: