Excel Function returning negative 0

I

ibertram

Hi there,

I am trying to run a function here but it seems that the function will
return a negative value only if the values are as follows.

A: 84.90
B: 57.16
C: 27.74

Excel function:
=IF((+A-B-C)=A,0,(+B+A-C))

if I am to play around with values B and C. I will be able to get a
positive 0.
i.e. 57.15, 27.75 or 57.17,27.73

Hope someone can help.
Thanks.
 
B

Bryan Hessey

I was never good at maths, but am I correct in thinking that

IF((+A-B-C)=A
is the same as
IF((-B-C)=0
and the same as
IF(B+C=0
and the opposite of
IF(B+C

which would then reduce to

=IF(B+C,A+B-C,0)

which should give zero only when B and C are equal and opposite, or
when A+B-C totals zero.

Does this help your thoughts?
 
D

damorrison

(-B-C)won't equal zero unless C=(-B)

where are the numbers coming from, are there decimals
 
J

Jerry W. Lewis

=(A-B-C)
(your plus sign is not needed) returns 1.06581410364015E-14. I can't
figure out where you think you are getting "negative 0", but I will
guess that you are probably mystified as to why this calculation is not
zero.

Excel (and almost all other general purpose software) does binary math.
Most terminating decimal fractions are non-terminating binary
fractions (just as 1/3 is a non-terminating decimal fraction) and hence
must be approximated. When you do math with approximate inputs, then it
should be no surprise if the outputs are only approximate. Consequently
your tests should allow for these approximations, as in
=IF(ROUND(A-B-C,2)=0,...
(assuming that you intended your formula to compare to zero instead of A)

Jerry
 
Top