No one is perfect, although some of us are more so than others. But when it comes to finding problems in complex Excel formulas there are ways to help you on the road to perfection.
So, you just wrote this amazing formula in Excel. It will cut you work time in half and may even win you the Nobel Prize in Spreadsheet. Only one problem. It doesn’t work.
At this point you have a few options:
• You can spend hours reading and re-reading the syntax hoping to find the offending character
• You can give up and try a less glamorous equation
• You can swallow your pride and bug the in-house Excel Geek
• OR… You can trace the error yourself!
Excel comes with the ability to Evaluate Expressions. It is easier to explain how it works by showing you an example.
These tables show all the farmers in the state by region and their livestock. But as you can see, the sum of Turkeys by region is not calculating. Here are the actual formulas I use.
As you can see, I am using an Array Formula to calculate the SUM of Turkeys (cells H$3:H$15) based on the region (cells $C$3:$C$15). The formula looks correct and should work, so why is it displaying 0’s?
NOTE: All Excel menu and screen layouts will be references from version 2010. These features exist in all versions, but in different locations.
To have Excel evaluate the formula for you, just click in the cell (in this case H20) and then go to the Formulas menu tab and click on Evaluate Formula.
This will open a window showing the current selected formula and what is being evaluated.
Let me explain what you are seeing in this window. The current formula being evaluated is listed under Reference, Sheet3$H$20. In the Evaluation section you see the formula in its current state with the next parameter to be evaluated underlined, $C$20. All you need to do is press the Evaluate button to see what Excel is doing step by step. After pressing it a few times you will get to this point:
Here we can see it is about to compare “Turkeys ” to “Turkeys”. BINGO, one of the Turkeys has a space at the end. No wonder it’s not working correctly. All I need to do is delete that hanging space and TAH-DAH!
It’s easy being an Excel Guru when you know how to use the tools.