This difference between Excel’s stored value for a cell and the displayed value happens all the time and it is important to understand.
Late last week we detailed a bug in Excel 2007 where the wrong number appears on the screen yet Excel ‘knows’ the right value for other calculations. This difference between Excel’s stored value for a cell and the displayed value happens all the time and it is important to understand.
Excel always calculates formulas to a high precision- 15 significant digits, probably more than you need so the displayed number is only part of what Excel uses to perform maths.
Many Excel worksheets involve money, displaying dollars and cents (ie two decimal places) but Excel is calculating based on fractions of a cent – and those fractions can add up.
Here’s the simple example Microsoft uses:
You have worked out the cost price of a product to $20.005 – Excel will display that as $20.01 because of rounding in Currency Format.
Add the cost price of two products each $20.005 and Excel will show a total of $40.01 – not $40.02 (ie the displayed values $20.01 plus $20.01).
A similar thing happens when adding up percentages – often the total won’t be 100% because of rounding.
The most extreme example of this is Excel’s display of dates. As we’ve noted previously in Office for Mere Mortals – all Excel dates are actually numbers (integers are days and fractions hours/minutes). It is only the display formatting of the cell that makes a date appear.
See what Excel sees
There’s no direct way to expose the ‘real’ numbers Excel uses in cell because all cells have some formatting to transform the number for human eyes.
If you’re trying to uncover a worksheet anomaly you can change the cell formatting to the ‘General’ format that will adapt to show as much of the cells value as possible.
Calculate what you see
Excel has an option to change the precision from the stored value to displayed value.
In Excel 2007 you’ll find it under Excel options | Advanced
In Excel 2003 the same choice is as Tools | Options | Calculation:
But be aware that changing to displayed precision may have unwanted consequences too – for example fractional values are dropped which can have an unexpected affect on calculations with large values. For example a price of $10.004 displays as $10.00 – if you work out the cost of 100,000 units the result will be different by $500 depending on which precision you use.
‘Precision as displayed’ applies to an entire worksheet and is usually avoided because much mathematical accuracy is lost. Someone looking at the worksheet and innocently changing a cell style could affect the results of the worksheet.
Most Excel users prefer to control precision in their formulas as required.
Changing precision in a formula
Aside from changing the precision for an entire worksheet there are some functions you can use to force the precision of numbers in a formula.
Some functions to workaround Excel display vs stored value:
Round() this will round the number to precision you want.
Int() will convert a number to an integer by rounding
Trunc() will convert a number to an integer by truncating the fractional part.
What about the bug?
The new bug is in Excel 2007 only – some formulas that have specific number results around the 65,535 mark will show ” 100,000 ” instead of the true value.
Microsoft has released a patch to deal with this bug.
- More nested IF options
- The CHOOSE option in Excel
- Workarounds for Excel’s addition problems
- More Excel addition strangeness
- Excel SUM anomaly
- Excel calc bug fix
- Excel’s problem with 65,535 & 65,536
- Dates in Excel 2003 – Part 1