basic addition of formulae

  • Thread starter Thread starter paul fowkes
  • Start date Start date
P

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
 
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.
Click to expand...

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
 
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)
 
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...
Click to expand...

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.
 
Formatting changes the display, not the value. Use the ROUND function
to change the value.

Jerry
Excel MVP
 
Back
Top