Troubleshooting Excel Formulas
Introduction
If you are an Excel user, you may have been writing Excel formulas from day one, or you may just be learning how helpful they can be. Either way, it's likely that you run into problems from time to time. Just like any other programming language or statistical tool, Excel provides a way to decompose a formula, however long it may be, and perform step-by-step calculations. This way, you can jump inside a nested formula and understand how it works.
In this guide, you will learn to troubleshoot Excel formulas by walking through a scenario based on calculating the distance between two coordinate points and then normalizing the resulted distance. You'll need to understand operator precedence for this guide. For a quick review, you can refer to Wikipedia: Order of operations.
Set Up the Data
Consider this table, which consists of five rows each with two coordinate points (x,y):
| X1 | Y1 | X2 | Y2 |
|---|---|---|---|
| -1 | 8 | 16 | 32 |
| -8 | 789.8 | 2 | 8 |
| 13.45 | -0.2 | 63 | -4 |
| -4 | -8000 | 130 | -7 |
| -7 | -8 | 9 | 150 |
First, calculate the Euclidean distance, or shortest distance, between each of the pairs. The Euclidean distance is represented as:
1d = sqrt( (x2 - x1)^2 + (y2 - y1)^2 )Create a new column named Euclidean Distance and use the formula above to arrive at the following table:
| X1 | Y1 | X2 | Y2 | Euclidean Distance |
|---|---|---|---|---|
| -1 | 8 | 16 | 32 | 29.41088234 |
| -8 | 789.8 | 2 | 8 | 781.8639524 |
| 13.45 | -0.2 | 63 | -4 | 49.69549778 |
| -4 | -8000 | 130 | -7 | 7994.123154 |
| -7 | -8 | 9 | 150 | 158.8080602 |
Notice that the resulting Euclidean Distance column values are not rounded up and they are spread across a range [29.4, 7994.1].
The next step is to normalize the distance values in a range [0, 1] using the given formula:
1x_current = (x_current - x_minimum) / (x_maximum - x_minimum)By applying the above formula to the Euclidean Distance column values, you get this table:
| X1 | Y1 | X2 | Y2 | Euclidean Distance | Normalized Euclidean Distance |
|---|---|---|---|---|---|
| -1 | 8 | 16 | 32 | 29.41088234 | 0 |
| -8 | 789.8 | 2 | 8 | 781.8639524 | 0.094473353 |
| 13.45 | -0.2 | 63 | -4 | 49.69549778 | 0.002546811 |
| -4 | -8000 | 130 | -7 | 7994.123154 | 1 |
| -7 | -8 | 9 | 150 | 158.8080602 | 0.016246309 |
Notice that all the values in the Normalized Euclidean Distance column lie in a range of [0, 1]. With the data setup completed, let's learn how Excel computes these formula outputs step by step.
Access the Evaluate Formula Tool
To troubleshoot any Excel formula, follow these steps:
- Select an appropriate cell to evaluate from a column (don't select a range of cells or the complete column)
- Click the Formulas tab
- Under Formula Auditing, click Evaluate Formula
Use the Evaluate Button
Select any value from the column and click on the Evaluate Formula tool. If cell E3, for example, is selected, this dialog box pops up:
In the dialog box, you can observe the following:
- A formula (
=SQRT((C3-A3)^2 + (D3-B3)^2)), which has been used to calculate the value of cell E3. - An underline below the term C3 in the formula. This is the first term that will be evaluated in this formula. As we go further evaluating the formula, whichever term has the underline below it will be evaluated according to the given value.
- Four buttons: Evaluate, Step In, Step Out, and Close.
The Evaluate button is used to evaluate the current term according to the given value—and the value can be a formula, too.
The Step In button is nested under the current underlined term and shows what is stored inside. If the term has a formula, you can step in further until you reach a constant value.
The Step Out button is used to undo what is done by the Step In button, i.e., to move up the formula hierarchy.
You will learn how to use the Step In and Step Out buttons in the next section. In this section, focus on how the Evaluate button works. Since you have selected cell E3, which has (-8, 2) as the (X1, X2) coordinates, it will take you a total of five Evaluate button clicks to calculate the first term of the Euclidean distance. Here's the breakdown of those five steps:
- C3 evaluated to 2
- A3 evaluated to -8
- Calculating 2 - -8 which gives you 10
- Removing the round brackets around 10
- Squaring the value to arrive at 100
Similarly, go ahead and evaluate the entire formula. After a total of 12 small operations, you will arrive at the value of cell E3: 781.8639524.
Use the Step In and Step Out Buttons
In the section above, you learned how useful the Evaluate button is. In this section, you will learn how to properly use the other two buttons.
Click on any one cell (say, cell F3) from the Normalized Euclidean Distance column to open up the Evaluate dialog box. Now, before you start evaluating, notice that the underline is below the E3 term, which already has a formula in it. Therefore, click on the Step In button to reveal the underlying formula, as shown:
You can keep going under the formula by clicking on the Step In button until it has reach a constant value (not a formula). Since you have already evaluated the formula in cell E3 in the previous section, you know there's only one more next step to go until it reaches a constant value and thus disables the Step In button, as shown:
To go back to the primary formula and click on the Step Out button two times. You will now have the evaluated value of the term E3. For the next three terms, the Step In button will not be enabled because all those terms are formulas written directly in the cell F3. Evaluate the complete formula to arrive at the final result of 0.094473353 .
Conclusion
You have learned how to use the Evaluate Formula tool, including its role in breaking down a nested formula and evaluating each term step-by-step. This will help you to analyze large nested formulas and understand how they work.