Addition problem, number always 2 cents off...Help
 

I have a little problem with my SUMs

I have 200 to 300 rows of A * B = C

Then I tell it to do a =sum(d1:d300)

All the cells are formated to currency, two decimal places.

when I add up the numbers with an adding machine I'm always 2 - 22
cents off. (2 cents on this sheet, 22 cents on another sheet....)

Can anyone help

Thank you

WTG

The issue is very likely to be rounding - that the product of each
multiplication has fractional cents that Excel considers in the SUM()
function, even though you don't see them fractional cents on screen

You can either modify your multiplication formula to be

=ROUND(A*B,2)

and then sum the rounded values, or you can use an array SUM() formula

=SUM(ROUND(range of values,2))
You need to commit this formula by pressing CTRL-SHIFT-ENTER, rather than
just Enter

Duke





"WTG" wrote:

I have a little problem with my SUMs

I have 200 to 300 rows of A * B = C

Then I tell it to do a =sum(d1:d300)

All the cells are formated to currency, two decimal places.

when I add up the numbers with an adding machine I'm always 2 - 22
cents off. (2 cents on this sheet, 22 cents on another sheet....)

Can anyone help

Thank you

WTG

A third option is Tools|Options|Calculation and check "Precision as
displayed". Note however that it would impact all calculations, not
just the specific one the OP asked about.

Jerry

Duke Carey wrote:

The issue is very likely to be rounding - that the product of each
multiplication has fractional cents that Excel considers in the SUM()
function, even though you don't see them fractional cents on screen

You can either modify your multiplication formula to be

=ROUND(A*B,2)

and then sum the rounded values, or you can use an array SUM() formula

=SUM(ROUND(range of values,2))
You need to commit this formula by pressing CTRL-SHIFT-ENTER, rather than
just Enter

Duke





"WTG" wrote:


I have a little problem with my SUMs

I have 200 to 300 rows of A * B = C

Then I tell it to do a =sum(d1:d300)

All the cells are formated to currency, two decimal places.

when I add up the numbers with an adding machine I'm always 2 - 22
cents off. (2 cents on this sheet, 22 cents on another sheet....)

Can anyone help

Thank you

WTG

array SUM() formula is
=ROUND(Sum(range of values),2)


"Duke Carey" wrote:

The issue is very likely to be rounding - that the product of each
multiplication has fractional cents that Excel considers in the SUM()
function, even though you don't see them fractional cents on screen

You can either modify your multiplication formula to be

=ROUND(A*B,2)

and then sum the rounded values, or you can use an array SUM() formula

=SUM(ROUND(range of values,2))
You need to commit this formula by pressing CTRL-SHIFT-ENTER, rather than
just Enter

Duke





"WTG" wrote:

I have a little problem with my SUMs

I have 200 to 300 rows of A * B = C

Then I tell it to do a =sum(d1:d300)

All the cells are formated to currency, two decimal places.

when I add up the numbers with an adding machine I'm always 2 - 22
cents off. (2 cents on this sheet, 22 cents on another sheet....)

Can anyone help

Thank you

WTG