Rounding problem or sum function not working correctly

[Stargazer980]

When using sum to add the three Tax amts when set to only 2 decimal places it
gives a total of $21.79 instead of $21.80. Dispalyed to thrird place for
detailed info. If you round each to the second decimal place it should total
$21.80.

Amt Tax Tax amt
$49.95 0.063 $3.147
$52.95 0.063 $3.336
$242.95 0.063 $15.306

Excel does not give $21.80 as total.

 

[Nick Hodge]

Stargazer

Formatting only 'truncates' the display, not the value that Excel will use
in subsequent calculations. When doing anything with money (Where 2
decimals is needed) all formula should be wrapped in a ROUND function

=ROUND(A1,2)

or similar. This will make the underlying value round to two decimals and
it then equals 21.80

Alternatively, you can set this as permanent by using
Tools>Options...>Calculation>Precision as displayed

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Nick Hodge]

Pleasure

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk