51. Excel Tips - Calculation in Excel
Calculation in Excel is a big topic, but I am going to try to give you the essentials about calculating formulae.
Calculation works by the creation of a calculation tree. This is a map of all cells, their dependents, and their dependents... etc.. To do the fastest calculation, Excel needs to go right to the end of the tree, and calculate those cells first. If on the other hand, Excel were to calculate each cell one by one, it could not guarantee that every cell had calculated correctly.
Lets see how this works... We have 3 cells... Cell1 is dependent on Cell3, and Cell3 is dependent on Cell2. Cell2 is just a number.
The formulae and values look like this:
Now suppose A3 changes to 2:
If we now calculate each cell one by one starting at the top cell, this is what happens:
NOTE: Cell A2 has the wrong value.. It should be 4. The reason it's wrong is that cell A4 should have been calculated first as A2 depends on A4.
The correct calculation should be:
Now Excel could complete this calculation by recalculating each of these cells again. In this case, as there are only 2 dependencies, the calculation would be complete. However, anyone who has designed complex spreadsheets will know that the amount of dependencies can be absolutely HUGE. Hence the most efficient way is for Excel to build a calculation tree, and work backwards.
By building a tree of dependencies, Excel knows to calculate cell A4 first, then A2... hence making the calculation efficient and quick.