Introduction

0

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.

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:

`1`

`d = 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:

`1`

`x_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.

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**

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.

0