The trouble with formulas

Discovered another Excel quirk today.

Humans see the numbers in a cell, while Excel sees the formula in the cell. The values are simply results of the formula; of secondary importance. Most of the time this isn't a problem, however if a user is applying functions to values that have formulas hidden behind them they can get unexpected results. To see this for yourself, the above site suggested you enter the formula "=RAND()" into some cells and then sort them.

So I did. I used "=ROUND(RAND()*100, 0)" as my formula to give me easy to see results. The left is cells with the formula. The right is after sorting those cells:

In this particular instance, when you change the value of a cell, the _Change event fires which triggers an internal recalculation of the formulas on the sheet. This can be turned off programatically. After turning off automatic calculation, my results look like this:

Thinking it through it's fairly obvious why you get unexpected results from sorting formulas: there is no good way to approach this problem. Either approach gives (eventually) unexpected results. If you only sort the values, and leave behind the formulas in their original cell, the values all change on recalculation. You could destroy the formula and only keep the values, but then you've gone and destroyed the user's formulas.

Many formulas are addressed relatively; for example instead of the cell A1 explicitly referencing the cell "H5" it could relatively addresses the cell 8 to the right and 5 down. If you move that relative address around, the value will change on recalculation. The problem is that the user can't know what to expect without doing tests (or finding others who have done these tests) to see how their version of Excel behaves. The best solution here is to copy just the values, paste them elsewhere and sort those, leaving the original formulas intact.

A wonderful quote from the above site (emphasis mine): "Excel bugs don't exist, but Excel has a lot of features."

0 things about

The trouble with formulas

Post a Comment

Copyright 2012 Phile not Found. See About
Powered by Blogger

"Whenever you find that you are on the side of the majority, it is time to pause and reflect."