Rounded numbers do not add properly.
 

I saw a post that asked this question and the guy who answered made him look
stupid but I agree with the man.

I use Excel to do accounting work. What happens is the numbers add as the
original number and not the rounded number. That can lead to some serious
miscalculations.

Is there a formula that can be used when multiplying or dividing that will
round the number to the nearest hundredth and have the column properly add
the rounded numbers? The way it is right now, it adds the whole number not
the rounded.

I think the IRS might have something to say if the pennies start adding up
to $100s of dollars in missed tax monies.

Stephanie,

Use Tools | Options.... Calculation tab, check "Precision as displayed"

HTH,
Bernie
MS Excel MVP


"Stephanie DeCou" <Stephanie wrote in
message ...
I saw a post that asked this question and the guy who answered made him
look
stupid but I agree with the man.

I use Excel to do accounting work. What happens is the numbers add as the
original number and not the rounded number. That can lead to some serious
miscalculations.

Is there a formula that can be used when multiplying or dividing that will
round the number to the nearest hundredth and have the column properly add
the rounded numbers? The way it is right now, it adds the whole number
not
the rounded.

I think the IRS might have something to say if the pennies start adding up
to $100s of dollars in missed tax monies.

Take a look he

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

In article ,
Stephanie DeCou <Stephanie wrote:

I saw a post that asked this question and the guy who answered made him look
stupid but I agree with the man.

I use Excel to do accounting work. What happens is the numbers add as the
original number and not the rounded number. That can lead to some serious
miscalculations.

Is there a formula that can be used when multiplying or dividing that will
round the number to the nearest hundredth and have the column properly add
the rounded numbers? The way it is right now, it adds the whole number not
the rounded.

I think the IRS might have something to say if the pennies start adding up
to $100s of dollars in missed tax monies.

That won't necessarily solve the problem. If Precision as displayed is
set, and all cells are set to 2 decimal places:

A1: 1.00
A2: =A1/3 == 0.33
A3: =A2 * 3 == 0.99
A4: =A1/3 * 3 == 1.00
A5: = A3 = A4 == FALSE

In article ,
"Bernie Deitrick" <deitbe @ consumer dot org wrote:

Use Tools | Options.... Calculation tab, check "Precision as displayed"

Here's another possible option.

For those times when you don't want to put round() formulas in all your cells:
1)Set the cell formats to display an appropriate number of decimal places.
2)Sum those cells using a variation of this formula:
=SUMPRODUCT(ROUND(A1:A10,2))
That function rounds each number to 2 decimal places before it is summed.

In my example, I put =1.004 in cells A1:A10 and set the format to 2 decimal
places
Each cell displays 1.00, but actuall contains 1.004.

Using A11: =Sum(A1:A10), I get 10.04

I want my total to be 10.00 (1.00x10)
Using A11: =SUMPRODUCT(ROUND(A1:A10,2)) I get 10.00.

Does that option help?

--
Regards,
Ron