Wrong Number Results

[Dee]

Hi,

In looking at the following figures, the first set are the
result of multiplying a quantity by a price. The amount
of 38.22 is the result of summing the figures of 6.25
through 13.75. The amount of 2.68 is the result of the
cell that contains 38.22 by 7% (tax). The amount of 3.07
is by summing the cells that contain 38.22 and 2.68, then
muliplying them by 7.5%. The amount of 43.96 is the grand
total.

When done via fomulas as described above, the grand total
is off by one cent. If I type in the figures manually,
this isn't a problem.

Can anyone explain why this is? Thanks!

6.25
2.90
5.97
9.35
13.75

38.22
2.68
3.07

43.96

 

[Ron Rosenfeld]

Hi,

In looking at the following figures, the first set are the
result of multiplying a quantity by a price. The amount
of 38.22 is the result of summing the figures of 6.25
through 13.75. The amount of 2.68 is the result of the
cell that contains 38.22 by 7% (tax). The amount of 3.07
is by summing the cells that contain 38.22 and 2.68, then
muliplying them by 7.5%. The amount of 43.96 is the grand
total.

When done via fomulas as described above, the grand total
is off by one cent. If I type in the figures manually,
this isn't a problem.

Can anyone explain why this is? Thanks!

6.25
2.90
5.97
9.35
13.75

38.22
2.68
3.07

43.96

Your numbers in set 1 are likely not stored exactly as displayed. The usual
solution is to round to two decimal places. Other solutions included
Tools/Options/...Precision as displayed; however this affects the entire
spreadsheet.

A solution I like is to change your SUM function to:

=SUM(ROUND(A1:A6,2))

entered as an *ARRAY* formula. (Hold down <control><shift> while hitting
<enter>).

An equivalent formula is the NON-array formula:

=SUMPRODUCT(ROUND(A1:A5,2))



--ron

 

[JE McGimpsey]

Take a look here:

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

 

[Bernard Liengme]

1) 38.22 * 7% = 2.6754
2) (2.6754+38.22)*7.5%=3.067155
3) 38.22 + 2.6754 + 3.067155= 43.962555 => 43.96 when rounded to 2 places
of decimal

but if we always round off
1) 38.22 *7% = 2.68
2) (2.68+38.22)*7.5% = 3.07
3) 38.22+ 2.68 + 3.07 = 43.97


You might want to do calculations with ROUND as in =ROUND(B7*7%,2) to get
answer rounded to nearest cent, or to use Tools|Option to set value to be
the same as displayed.

Losing a cent is very common when you do multiplication with small numbers
(7%=0.07) and then round to 2 decimal places. It is a fact of math.

 

[dee]

Thanks to all for your answers.