Podcast: Play in new window | Download
Welcome back to the podcast! This week’s edition: Another Case of Formula Frenzy.
It was a dry and brisk November evening when I got the call. Sitting at my desk with the blinds cracked just enough to allow the last rays of sunlight from the dying day to creep through, the ringing of the phone shattered the silence like a brick through a pane of glass. The dame was one-half shrill hysteria, two-thirds livid frustration, and just a splash of hopeless desperation. The recipe for a typical batch of Formula Frenzy. Combine and shake well; serve chilled and aggravated.
She had just written a formula in an Excel worksheet that was supposed to calculate her income and bill payments and return a tidy little budget for monthly spending. Instead, it returned the dreaded #N/A error. This is where I come in.
I’m a formula detective. It’s my job to examine the data, peer deep into the dark underlying elements that comprise a failing formula, and eliminate the errors. I told her I’d take the case. After all, I know the usual suspects; more importantly, I know the tricks to undo their devilry.
It’s not that hard, really. Most of the tools needed for the job are built right into the Excel application and readily available. I prefer to start with the “Evaluate Formula” feature. This one really lets you step right into the formula and break it down to pinpoint where the error occurs. That kind of hands-on approach takes a lot of the guess-work out of a long and complex formula, and has helped me successfully close many cases of Formula Frenzy.
For some of the simpler formulas, I tend to go right to the source: the Formula Bar. If you want to see what’s happening behind the scenes, this is for you. You can use the F9 key in the Formula Bar to convert a highlighted portion of a formula to its resulting value.
Another good trick to know is how to force all the formulas in the worksheet to show themselves. Sometimes it’s hard to tell what’s lurking underneath those cells, but the “Show Formulas” feature (also called “Formula Auditing Mode” in some versions of Excel) will bring all the formulas out of hiding.
It can also help to know where a formula’s coming from. Sometimes, being able to see which cells directly impact a formula’s result can shed some light on why it might be producing an error. As it turns out, Excel couldn’t have made this any easier. You can use the Trace Precedents arrows to point out exactly which cells a formula is affected by. The Trace Dependents arrows will show you which other cells are affected by that cell.
Then there are those cases of Formula Frenzy that can really leave your head spinning. This is typically a symptom of Circular References, which can occur when Excel is trying to calculate a formula that includes a reference to the cell it’s in. Makes me dizzy just thinking about it.
I’ve seen some bad cases of Formula Frenzy in my line of work. It’s not something I’d wish on anyone, so I’m leaving you with some tips that should help you avoid having to call me to bail you out when a formula goes awry.
The tips are posted on the blog entry for this podcast at www.productiveUpodcast.com, and they include:
- Let’s Evaluate: Troubleshooting faulty formulas with Excel’s “Evaluate Formula” tool
- Behind the Scenes: Using F9 to evaluate formulas in the Formula Bar
- Genealogy of a Formula: Using the Trace Precedents and Trace Dependents arrows
- Revelio Formulae: How to toggle the display of formulas on a worksheet
- Going in Circles: Understanding circular references
- Hidden in Plain Sight: Using the N function to add comments inside formulas
This should be enough to help you get your own formula detective agency up and running. Next time you come across a formula playing by its own rules, you’ll know just where to start snooping to solve the mystery of the #N/A error.
That’s all for this edition of the podcast. Until next week, be sure to head over to iTunes to write a review of the podcast, or drop us a line on Twitter @productiveUcast.
Music courtesy of Clayton & Fulcrum
