basic addition of formulae

[paul fowkes]

I wonder if somebody could tell me with a basic addition problem.

I put a formula in a cell, to say total 2 other cells e.g =sum(a1 x a2 ) or
42.5 x .057

The sum of the formulated cell is 24.225. I then format the cell to 2 x
decimal places....24.23...

If I then total a complete column of these formulae ( using an addition
formula) and format to 2 x decimal places the answer given is incorrect.

The computer still thinks it is totalling to the number places it goes
to...in this example 3 rather than the rounded up/down number formatted.

This means that I can't total money accurrately.

Am I missing something......Can somebody help me out please

 

[Jan Karel Pieterse]

Hi Paul,

If I then total a complete column of these formulae ( using an addition
formula) and format to 2 x decimal places the answer given is incorrect.

The computer still thinks it is totalling to the number places it goes
to...in this example 3 rather than the rounded up/down number formatted.

This means that I can't total money accurrately.

This is by design: Excel always calculates with 15 digits, regardless of
how those numbers are displayed on screen. UNLESS you tell it otherwise.

There are two ways to overcome this:

1. Use rounding (the ROUND worksheet function and sum the rounded numbers)

2. Use Tools, options, general, precision as displayed. Warning: this
permanently removes all digits that are invisible and may affect other
calculations in your workbook.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Frank Kabel]

Hi
just formating does not change the value of a cell. If you
only need two decimals use the ROUND function. e.g.
=ROUND(A1*A2,2)

 

[Robert Christie]

Hi

Paul wrote

42.5 x .057
The sum of the formulated cell is 24.225. I then format the cell to 2 x
decimal places....24.23...

I don't know if .057 was a typo on your part or not.
But 42.5 x .057 = 2.4225 round by excel to 2 decimal
places would show as = 2.42
I second Frank and Jan answers on Excel's handling of
numbers.

Regards Bob C.

 

[Jerry W. Lewis]

Formatting changes the display, not the value. Use the ROUND function
to change the value.

Jerry
Excel MVP