Integrated Circuit (Computer chip) by Kilby // PerfectXL Spreadsheet Validation

Calculate Formulas Only Once!

Sometimes, you need to use the exact same formula, based on the same numbers, in several other formulas. An example of this might be the calculation for inflation, which is the needed in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Make sure that you calculate your formulas only once!

Time Consuming and Error Prone

Spreadsheets live a long time, and during this lifespan, your calculation of inflation is most likely going to change. When the calculation is repeated in multiple formulas, and the inflation changes, you will need to go back and adjust all of those formulas. This process is not only time consuming, it is also likely to result in errors. You could easily forget to modify the inflation formula in one or more locations. Furthermore, calculations that are repeated as part of a formula make your spreadsheet slower, this is because Excel really calculates each formula, and it will calculate it as many times as you write it out.

 The Solution

A better option is to place the sub formula in a separate cell, and refer back to that cell in the other formulas that make use of it. This has several advantages. Firstly, the spreadsheets will be easier to maintain since you only have to adjust one cell in case of a change. Secondly, the spreadsheet is easier to understand as the formula is placed separately, and you can now add a label to describe it, and you don’t need to search for it in other formulas. Finally, when you place the formula in a cell of its own, Excel calculates the formula only once and then “sends” the results to the cells that use it. This leads to a more efficient calculation and your spreadsheet does not get unnecessarily slow.

Check your Spreadsheet

You might be thinking, “well this might be true, but what about the spreadsheets I built before that still have these double calculations.” Well we have a solution for that! Our tooling, PerfectXL, highlights every calculation that is being made more than once, and notifies you as to where those calculations can be found and then changed. Create a free account now and check it today!

The image shows the PerfectXL overview of duplicate calculations in a spreadsheet, as one type of risk in the category ‘Structure Issues’

How can PerfectXL help you?

Share your questions with us, we are more than happy to help you. We will get back to you within 48 hours.