Incorrect result for simple SUM formula

[VivienW]

Hello,

Does anyone know why Excel sometimes give an incorrect result when adding
the contents of cells? It may only be 1p out but even if I increase the
decimal places it doesn't seem to make sense as the decimal places are
showing a lesser number than the +1p error.

e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places
result becomes 13.74643

 

[Ms-Exl-Learner]

What is the values you are having in D12 to D22?

 

[VivienW]

The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12)

It calculates the price of items sold as a unit or by weight. The final
cell containing the error totals the individual items.

 

[David Biddulph]

And what were the extra decimal places in D12 to D22 inclusive?

If you want to round the contents of each of the cells D12 to D22 to 2
decimal places and then add the results, use =SUM(ROUND(B12:B22,2)) and
enter as an array formula (Control Shift Enter).

 

[David Biddulph]

Yes. So how many decimal places does the result of that calculation have?
[And I don't mean how many decimal places have you currently formatted to
*display*, but how many decimal places are there in the number *stored* in
the cell, because it is those stored numbers that you are adding.] See my
reply elsewhere in this thread.

 

[VivienW]

Thanks David,

I was really only hoping for a reason Excel does not always give the correct
result.

I am trying to replicate the file but it won't give the wrong answer as I'm
not using the same figures to start with, so am unable to tell you how many
decimal places are involved.

I will try out the formula you suggest.

Thanks again.

 

[David Biddulph]

I think you'll find that Excel *is* giving the correct result (subject to
its limitation to 15 significant figures, and that wouldn't give the sort of
discrepancy which your figures describe), but it will give the result to the
question you asked it, not necessarily the question that you might have
intended to ask.

In general, if you find that the answer isn't what you expected, it's always
worth checking the data values which are being fed into the formula.

 

[Joe User]

1> e.g. =SUM(D1222) result 13.75 (should be 13.76) increase
1> decimal places result becomes 13.74643

2> The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12)
2> It calculates the price of items sold as a unit or by weight.

3> I was really only hoping for a reason Excel does not always
3> give the correct result.

One likely explanation: the results in some or all of D1222 [sic] are not
accurate to just 2 decimal places. For example, if A12 is zero, B12 is 123
and D12 is 0.125, the result of B12*D12 might be displayed as 15.38 due to
formatting, but it is really 15.375.

Temporarily, format D1222 to 5 decimal places to confirm that that is the
problem.

Since that formula is intended to compute price, it would be better to
write:

=ROUND(IF(A12=0, B12*D12, A12*D12), 2)

Nonetheless, it would also be wise to write:

=ROUND(SUM(D1222), 2)

(Note: There seems to be a circular reference in your examples. You are
summing D1222, but you say the formula in each cell is, e.g,
IF(A12=0,B12*D12,A12*D12) presumably in D12. I suspect there has been a
change in the spreadsheet that caused a renaming of cells. Presumably, that
is not germane to the problem.)

That avoids another possible problem with arithmetic applied to numbers with
decimal fractions. To illustrate, try the following:

=IF(10.1 - 0.1 = 0.1, TRUE)

That results in FALSE(!). Again, this problem is avoided by the prudent use
of ROUND, in this case:

=IF(ROUND(10.1 - 0.1, 1) = 0.1, TRUE)

That example demonstrates another possible explanation of your problem with
SUM: the way that Excel (and most applications) store and perform
arithmetic on numbers with decimal fractions, namely "binary floating
point".

I believe that is not likely to be your primary problem because of the
magnitude of the unexpected result of SUM, namely 13.74643 instead of
13.75. But it might be a contributing factor.

Again, the remedy to both problems is the same: the pervasive, but prudent
use of ROUND. Although formatting can be used to round the __displayed__
value, formatting does not change the underlying __actual__ value.


----- original message -----

Thanks David,

I was really only hoping for a reason Excel does not always give the
correct
result.

I am trying to replicate the file but it won't give the wrong answer as
I'm
not using the same figures to start with, so am unable to tell you how
many
decimal places are involved.

I will try out the formula you suggest.

Thanks again.

----- another message ----

 

[Ron Rosenfeld]

Hello,

Does anyone know why Excel sometimes give an incorrect result when adding
the contents of cells? It may only be 1p out but even if I increase the
decimal places it doesn't seem to make sense as the decimal places are
showing a lesser number than the +1p error.

e.g. =SUM(D1222) result 13.75 (should be 13.76) increase decimal places
result becomes 13.74643

It is very unlikely that the result in Excel is incorrect. What is more likely
is that the *actual* values in D1222 add up to 13.74643.

You are probably making the common error of assuming that what is being
displayed in D1222 alters the precision of what is being *stored* in D1222.

If you expand the display of D1222 to, let us say, 5+ decimals, you will
probably determine that Excel is adding correctly.

If you are only interested in two decimal precision, then you should add a
Rounding function.

e.g. =round(IF(A12=0,B12*D12,A12*D12),2)


--ron

 

[Joe User]

Errata....

=IF(10.1 - 0.1 = 0.1, TRUE)

Of course, that should be:

=IF(10.1 - 10 = 0.1, TRUE)


----- original message -----

1> e.g. =SUM(D1222) result 13.75 (should be 13.76) increase
1> decimal places result becomes 13.74643

2> The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12)
2> It calculates the price of items sold as a unit or by weight.

3> I was really only hoping for a reason Excel does not always
3> give the correct result.

One likely explanation: the results in some or all of D1222 [sic] are
not accurate to just 2 decimal places. For example, if A12 is zero, B12
is 123 and D12 is 0.125, the result of B12*D12 might be displayed as 15.38
due to formatting, but it is really 15.375.

Temporarily, format D1222 to 5 decimal places to confirm that that is
the problem.

Since that formula is intended to compute price, it would be better to
write:

=ROUND(IF(A12=0, B12*D12, A12*D12), 2)

Nonetheless, it would also be wise to write:

=ROUND(SUM(D1222), 2)

(Note: There seems to be a circular reference in your examples. You are
summing D1222, but you say the formula in each cell is, e.g,
IF(A12=0,B12*D12,A12*D12) presumably in D12. I suspect there has been a
change in the spreadsheet that caused a renaming of cells. Presumably,
that is not germane to the problem.)

That avoids another possible problem with arithmetic applied to numbers
with decimal fractions. To illustrate, try the following:

=IF(10.1 - 0.1 = 0.1, TRUE)

That results in FALSE(!). Again, this problem is avoided by the prudent
use of ROUND, in this case:

=IF(ROUND(10.1 - 0.1, 1) = 0.1, TRUE)

That example demonstrates another possible explanation of your problem
with SUM: the way that Excel (and most applications) store and perform
arithmetic on numbers with decimal fractions, namely "binary floating
point".

I believe that is not likely to be your primary problem because of the
magnitude of the unexpected result of SUM, namely 13.74643 instead of
13.75. But it might be a contributing factor.

Again, the remedy to both problems is the same: the pervasive, but
prudent use of ROUND. Although formatting can be used to round the
__displayed__ value, formatting does not change the underlying __actual__
value.


----- original message -----

Thanks David,

I was really only hoping for a reason Excel does not always give the
correct
result.

I am trying to replicate the file but it won't give the wrong answer as
I'm
not using the same figures to start with, so am unable to tell you how
many
decimal places are involved.

I will try out the formula you suggest.

Thanks again.

----- another message ----

The value in each is a formula e.g. =IF(A12=0,B12*D12,A12*D12)

It calculates the price of items sold as a unit or by weight. The final
cell containing the error totals the individual items.