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

Excel Guideline 03: Calculate Formulas Only Once :: PerfectXL

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.

Calculate Formulas Only Once - Slow Spreadsheet :: PerfectXL

All Subformulas in One Worksheet

When you have several sub formulas that are used multiple times, it might be better to place all of them together on one worksheet called, for example “parameters” or “calculations”. This helps future users to understand and maintain the sheet. This also helps a lot with maintainability, especially when these variable formulas change with some regularity, it doesn’t cost the builder much time or energy at all, but makes the spreadsheet much easier to understand for outside users.

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!

Calculate Formulas Only Once - Duplicate Formulas in PerfectXL :: PerfectXL

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

Never miss a Guideline!