Simple calculation off by 2.00

[Charles Howse]

Hi,
OS X Tiger 10.4.3, with Excel 2004 for Mac, v.11.2 (050714)

My formula is so simple, I could almost do it in my head, but Excel gets it
wrong!

The formula is: =E5*D1
Where E5=4 and D1=12.38
The format for both cells is 'Number' with 2 decimal places.

Paper and pen gets 49.52, Excel gets 49.50.

What could be wrong?

Thanks,
Charles

 

[JE McGimpsey]

Hi,
OS X Tiger 10.4.3, with Excel 2004 for Mac, v.11.2 (050714)

My formula is so simple, I could almost do it in my head, but Excel gets it
wrong!

The formula is: =E5*D1
Where E5=4 and D1=12.38
The format for both cells is 'Number' with 2 decimal places.

Paper and pen gets 49.52, Excel gets 49.50.

What could be wrong?

Either E5 or D1 is a calculated value, right?

Unless you have Precision as Displayed selected
(Preferences/Calculation), XL calculates with the values stored in the
cells, rather than the value displayed. I'll bet that D1 actually
calculates to 12.375, but since you have 2 decimal places specified in
your format, the display is rounded to 12.38.

Note that Precision as Displayed affects your entire workbook - and may
give undesired results.

See

http://www.mcgimpsey.com/excel/pennyoff.html

for a more extensive explanation.

 

[Charles Howse]

Either E5 or D1 is a calculated value, right?

Unless you have Precision as Displayed selected
(Preferences/Calculation), XL calculates with the values stored in the
cells, rather than the value displayed. I'll bet that D1 actually
calculates to 12.375, but since you have 2 decimal places specified in
your format, the display is rounded to 12.38.

Note that Precision as Displayed affects your entire workbook - and may
give undesired results.

See

http://www.mcgimpsey.com/excel/pennyoff.html

for a more extensive explanation.

Thanks, JE! That was the problem.
When I increased the decimals to 3, it showed 12.375.
The explanation on your web page is excellent, but I still think that's
quirky behavior. ;-)