Errata....

I wrote:
=IF(10.1 - 0.1 = 0.1, TRUE)

Of course, that should be:

=IF(10.1 - 10 = 0.1, TRUE)


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

"Joe User" <joeu2004 wrote in message
...
"VivienW" wrote in several postings:
1 e.g. =SUM(D12:D22) 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 D12:D22 [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 D12:D22 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(D12:D22), 2)

(Note: There seems to be a circular reference in your examples. You are
summing D12:D22, 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 -----

"VivienW" wrote in 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.
--
Cheers, V


"David Biddulph" wrote:

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

"VivienW" wrote in message
...
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(D12:D22) result 13.75 (should be 13.76) increase decimal
places
result becomes 13.74643
--
Cheers, V


----- another message ----

"VivienW" wrote in 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.

--
Cheers, V