Simple Excel Formula - Incorrect Results

[Rick]

Re: Excel 2000 (9.0.3821 SR-1)
I have a spreadsheet with cells formatted as currency with
2 decimal point precision.
I am adding two columns of 11 cells each (B3:B13) and
(C3:C13) using the SUM operator and storing the results in
B18 and C18 respectively.
I am dividing B18 and C18 each by 11 to get the average
and storing the result in B20 and C20 respectively.
All of the formulas and results operate correctly up to
this point.
B20 contains the value $42.57. C20 contains the value
$60.45.
Cell B25 contains the formula =(B20+C20) and gives a
result of $103.01 instead of $103.02.
No matter how I arrive at this calculation, the result is
always one cent off. Further, even if I increase the
precision to as many as 7 or 8 decimal places, the
addition is still wrong.
I have checked and re-checked Excel option settings as
have others in my office.
Can anyone tell me what's going on here?
I would be happy to email this spreadsheet if that would
help.

 

[Charles Williams]

Hi Rick,

The default is for Excel to calculate with full 15 digit precision
regardless of formatting.

Formatting normally only affects what Excel displays, not what it
calculates.

You can change this behaviour by setting
Tools-->Options-->Calculation-->Precision as Displayed, but make sure you
make a copy of your workbook before you try this as it changes not only the
results of calculations but all your constants as well.


Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Paul]

Precision as displayed is a setting for the whole workbook. That's fine if
you are sure that is what you want. It can be complete overkill, however. My
personal preference is to leave this not set, but instead to explicitly
round appropriate calculations to the required precision. That way, I can be
sure it's doing what I want it to do. A useful rule of thumb is to round
whenever a multiplication or division takes place, if the result is required
to be to a specific precision (for example, money values).
=ROUND(Your Formula,2)