Problem withe ABS Function

[M and D]

I've got an unusual problem with the ABS (absolute value) function in =
Excel 2002 SP 3 running on Windows XP Home SP 2.

A cell in my worksheet performs this simple calculation: =
ABS(SUM(E37:J37)-SUM(K37:M37)). The calculation should normally result =
in a value of zero. If the calculation results in anything other than =
zero, my worksheet will alert me to that fact.

Something funny was going on. The cell was showing a value of zero, but =
my worksheet was still alerting me that the cell was something other =
than zero.

From Tools > Formula Auditing > Evaluate Formula I made this startling =
discovery:

The calculation evaluates to: ABS(12,182.23 - 12,182.23), which in turn =
does NOT evaluate to ABS(0). Instead, it evaluates to =
ABS(-1.81898940354586E-12)!

What the...where did -1.81898940354586E-12 come from? Have I done =
something wrong?

Steven

[JMB]

See http://www.cpearson.com/excel/rounding.htm

"M and D" wrote:

> I've got an unusual problem with the ABS (absolute value) function in Excel 2002 SP 3 running on Windows XP Home SP 2.
>
> A cell in my worksheet performs this simple calculation: ABS(SUM(E37:J37)-SUM(K37:M37)). The calculation should normally result in a value of zero. If the calculation results in anything other than zero, my worksheet will alert me to that fact.
>
> Something funny was going on. The cell was showing a value of zero, but my worksheet was still alerting me that the cell was something other than zero.
>
> From Tools > Formula Auditing > Evaluate Formula I made this startling discovery:
>
> The calculation evaluates to: ABS(12,182.23 - 12,182.23), which in turn does NOT evaluate to ABS(0). Instead, it evaluates to ABS(-1.81898940354586E-12)!
>
> What the...where did -1.81898940354586E-12 come from? Have I done something wrong?
>
> Steven
>
>
>

[M and D]

Thank you for that (and for reading my post).

While I understand and accept the point the article is making, I'm =
afraid I don't see how it applies to my situation.=20

First, all the arithmatic throughout my spreadsheet is adding or =
subtracting, so I don't see where there could be an issue with rounding.

Second, other cells in my worksheet that perform the identical =
calculation, albeit on different ranges of cells, produce the correct =
result.

Finally, if I get rid of the ABS function, leaving the calculation at =
SUM(E37:J37)-SUM(K37:M37), the calculation evaluates to zero and my =
worksheet does not alert me that the cell is something other than zero. =
This is why I suspected it has something to do with the ABS function.

I suppose I could experiment by putting the ABS function in different =
places, but I'd still like to know why this is happening (or if there's =
something I'm still not 'getting'.)

I'm going to sleep now, so please don't be offended if I don't write =
again for a while.

Steven



"JMB" <[email protected]> wrote in message =
news:[email protected]...
> See http://www.cpearson.com/excel/rounding.htm
>=20
> "M and D" wrote:
>=20
>> I've got an unusual problem with the ABS (absolute value) function in =
Excel 2002 SP 3 running on Windows XP Home SP 2.
>>=20
>> A cell in my worksheet performs this simple calculation: =
ABS(SUM(E37:J37)-SUM(K37:M37)). The calculation should normally result =
in a value of zero. If the calculation results in anything other than =
zero, my worksheet will alert me to that fact.
>>=20
>> Something funny was going on. The cell was showing a value of zero, =
but my worksheet was still alerting me that the cell was something other =
than zero.
>>=20
>> From Tools > Formula Auditing > Evaluate Formula I made this =
startling discovery:
>>=20
>> The calculation evaluates to: ABS(12,182.23 - 12,182.23), which in =
turn does NOT evaluate to ABS(0). Instead, it evaluates to =
ABS(-1.81898940354586E-12)!
>>=20
>> What the...where did -1.81898940354586E-12 come from? Have I done =
something wrong?
>>=20
>> Steven
>>=20
>>=20
>>

[Bob Phillips]

It may not seem so, but it does, because is using internal floating point
arithmetic in its calculations, and that has a limited precision which can
cause this sort of problem. If you are interested in understand it, look at
some of these posts http://tinyurl.com/f4kan by Jerry W. Lewis, he knows
what he is talking about

You might be able to get around it by rounding each sum to a fixed number of
decimal places.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"M and D" <[email protected]> wrote in message
news:[email protected]...
Thank you for that (and for reading my post).

While I understand and accept the point the article is making, I'm afraid I
don't see how it applies to my situation.

First, all the arithmatic throughout my spreadsheet is adding or
subtracting, so I don't see where there could be an issue with rounding.

Second, other cells in my worksheet that perform the identical calculation,
albeit on different ranges of cells, produce the correct result.

Finally, if I get rid of the ABS function, leaving the calculation at
SUM(E37:J37)-SUM(K37:M37), the calculation evaluates to zero and my
worksheet does not alert me that the cell is something other than zero. This
is why I suspected it has something to do with the ABS function.

I suppose I could experiment by putting the ABS function in different
places, but I'd still like to know why this is happening (or if there's
something I'm still not 'getting'.)

I'm going to sleep now, so please don't be offended if I don't write again
for a while.

Steven



"JMB" <[email protected]> wrote in message
news:[email protected]...
> See http://www.cpearson.com/excel/rounding.htm
>
> "M and D" wrote:
>
>> I've got an unusual problem with the ABS (absolute value) function in
Excel 2002 SP 3 running on Windows XP Home SP 2.
>>
>> A cell in my worksheet performs this simple calculation:
ABS(SUM(E37:J37)-SUM(K37:M37)). The calculation should normally result in a
value of zero. If the calculation results in anything other than zero, my
worksheet will alert me to that fact.
>>
>> Something funny was going on. The cell was showing a value of zero, but
my worksheet was still alerting me that the cell was something other than
zero.
>>
>> From Tools > Formula Auditing > Evaluate Formula I made this startling
discovery:
>>
>> The calculation evaluates to: ABS(12,182.23 - 12,182.23), which in turn
does NOT evaluate to ABS(0). Instead, it evaluates to
ABS(-1.81898940354586E-12)!
>>
>> What the...where did -1.81898940354586E-12 come from? Have I done
something wrong?
>>
>> Steven
>>
>>
>>

[Jerry W. Lewis]

It applies to your situation, because most decimal fractions are
nonterminating binary fractions that must be approximated. When you do math
with approximate inputs, naturally the output will only be approximate.

If you enter 12182.23, you actually get
12182.22999999999956344254314899444580078125. You cannot directly see this
value, because Excel will display no more than 15 digits, but you can detect
it by subtraction. There are in fact 55 distinct numbers that Excel can
represent which all display as 12182.2300000000, yet can differ from each
other by as much as 1.00044417195022E-10.

What has happened to you is the binary equivalent of
((1/3)+(1/3))-(2/3) = 0.3333+0.3333-0.6667 = -0.0001
The decimal math is right, but the answer is not zero because of the initial
decimal approximations to non-terminating decimal fractions.

If you are just adding and subtracting 2 decimal place numbers, then
rounding calculation results to 2 decimal places (especially for comparisons
to specific values or other calculations) will do no violence to your intent
and will avoid surprises like this.

Alternately, you could avoid the issue altogether by doing your accounting
in pennies instead of dollars; integers are exactly representable.

If you want to learn more about the internal representation of numbers, you
might find my VBA functions at
http://groups.google.com/group/micro...06871cf92f8465
to be useful.

Thanks for the plug, Bob.

Jerry

"M and D" wrote:

> Thank you for that (and for reading my post).
>
> While I understand and accept the point the article is making, I'm afraid I don't see how it applies to my situation.
>
> First, all the arithmatic throughout my spreadsheet is adding or subtracting, so I don't see where there could be an issue with rounding.
>
> Second, other cells in my worksheet that perform the identical calculation, albeit on different ranges of cells, produce the correct result.
>
> Finally, if I get rid of the ABS function, leaving the calculation at SUM(E37:J37)-SUM(K37:M37), the calculation evaluates to zero and my worksheet does not alert me that the cell is something other than zero. This is why I suspected it has something to do with the ABS function.
>
> I suppose I could experiment by putting the ABS function in different places, but I'd still like to know why this is happening (or if there's something I'm still not 'getting'.)
>
> I'm going to sleep now, so please don't be offended if I don't write again for a while.
>
> Steven
>
>
>
> "JMB" <[email protected]> wrote in message news:[email protected]...
> > See http://www.cpearson.com/excel/rounding.htm
> >
> > "M and D" wrote:
> >
> >> I've got an unusual problem with the ABS (absolute value) function in Excel 2002 SP 3 running on Windows XP Home SP 2.
> >>
> >> A cell in my worksheet performs this simple calculation: ABS(SUM(E37:J37)-SUM(K37:M37)). The calculation should normally result in a value of zero. If the calculation results in anything other than zero, my worksheet will alert me to that fact.
> >>
> >> Something funny was going on. The cell was showing a value of zero, but my worksheet was still alerting me that the cell was something other than zero.
> >>
> >> From Tools > Formula Auditing > Evaluate Formula I made this startling discovery:
> >>
> >> The calculation evaluates to: ABS(12,182.23 - 12,182.23), which in turn does NOT evaluate to ABS(0). Instead, it evaluates to ABS(-1.81898940354586E-12)!
> >>
> >> What the...where did -1.81898940354586E-12 come from? Have I done something wrong?
> >>
> >> Steven
> >>
> >>
> >>
>

[M and D]

Thank both of you very much for taking the time to explain this to me. I =
did finally 'get it'. It's all about the way numbers are represented =
inside Excel - inside computers, really. And the fact that the same =
calculation (described in my original post) using different ranges =
worked without a problem was just a happy coincidence.

I'll use rounding to resolve my original "problem".

That was some pretty fancy (to me) math you used, Jerry, but it =
illustrated your point very well. The next time someone asks me if I'm =
interested in doubles, I can say "do you mean tennis or floating point =
arithmetic?"

Steven

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message =
news:[email protected]...
> It applies to your situation, because most decimal fractions are=20
> nonterminating binary fractions that must be approximated. When you =
do math=20
> with approximate inputs, naturally the output will only be =
approximate.
>=20
> If you enter 12182.23, you actually get=20
> 12182.22999999999956344254314899444580078125. You cannot directly see =
this=20
> value, because Excel will display no more than 15 digits, but you can =
detect=20
> it by subtraction. There are in fact 55 distinct numbers that Excel =
can=20
> represent which all display as 12182.2300000000, yet can differ from =
each=20
> other by as much as 1.00044417195022E-10.
>=20
> What has happened to you is the binary equivalent of
> ((1/3)+(1/3))-(2/3) =3D 0.3333+0.3333-0.6667 =3D -0.0001
> The decimal math is right, but the answer is not zero because of the =
initial=20
> decimal approximations to non-terminating decimal fractions.
>=20
> If you are just adding and subtracting 2 decimal place numbers, then=20
> rounding calculation results to 2 decimal places (especially for =
comparisons=20
> to specific values or other calculations) will do no violence to your =
intent=20
> and will avoid surprises like this.
>=20
> Alternately, you could avoid the issue altogether by doing your =
accounting=20
> in pennies instead of dollars; integers are exactly representable.
>=20
> If you want to learn more about the internal representation of =
numbers, you=20
> might find my VBA functions at=20
> =
http://groups.google.com/group/micro...06871cf92f846=
5
> to be useful.
>=20
> Thanks for the plug, Bob.
>=20
> Jerry
>=20
> "M and D" wrote:
>=20
>> Thank you for that (and for reading my post).
>>=20
>> While I understand and accept the point the article is making, I'm =
afraid I don't see how it applies to my situation.=20
>>=20
>> First, all the arithmatic throughout my spreadsheet is adding or =
subtracting, so I don't see where there could be an issue with rounding.
>>=20
>> Second, other cells in my worksheet that perform the identical =
calculation, albeit on different ranges of cells, produce the correct =
result.
>>=20
>> Finally, if I get rid of the ABS function, leaving the calculation at =
SUM(E37:J37)-SUM(K37:M37), the calculation evaluates to zero and my =
worksheet does not alert me that the cell is something other than zero. =
This is why I suspected it has something to do with the ABS function.
>>=20
>> I suppose I could experiment by putting the ABS function in different =
places, but I'd still like to know why this is happening (or if there's =
something I'm still not 'getting'.)
>>=20
>> I'm going to sleep now, so please don't be offended if I don't write =
again for a while.
>>=20
>> Steven
>>=20
>>=20
>>=20
>> "JMB" <[email protected]> wrote in message =
news:[email protected]...
>> > See http://www.cpearson.com/excel/rounding.htm
>> >=20
>> > "M and D" wrote:
>> >=20
>> >> I've got an unusual problem with the ABS (absolute value) function =
in Excel 2002 SP 3 running on Windows XP Home SP 2.
>> >>=20
>> >> A cell in my worksheet performs this simple calculation: =
ABS(SUM(E37:J37)-SUM(K37:M37)). The calculation should normally result =
in a value of zero. If the calculation results in anything other than =
zero, my worksheet will alert me to that fact.
>> >>=20
>> >> Something funny was going on. The cell was showing a value of =
zero, but my worksheet was still alerting me that the cell was something =
other than zero.
>> >>=20
>> >> From Tools > Formula Auditing > Evaluate Formula I made this =
startling discovery:
>> >>=20
>> >> The calculation evaluates to: ABS(12,182.23 - 12,182.23), which in =
turn does NOT evaluate to ABS(0). Instead, it evaluates to =
ABS(-1.81898940354586E-12)!
>> >>=20
>> >> What the...where did -1.81898940354586E-12 come from? Have I done =
something wrong?
>> >>=20
>> >> Steven
>> >>=20
>> >>=20
>> >>
>>

[Jerry W. Lewis]

"M and D" wrote:

> Thank both of you very much for taking the time to explain this to me. I did
> finally 'get it'. It's all about the way numbers are represented inside Excel - inside
> computers, really. And the fact that the same calculation (described in my original
> post) using different ranges worked without a problem was just a happy
> coincidence.
>
> I'll use rounding to resolve my original "problem".

Glad it helped

> That was some pretty fancy (to me) math you used, Jerry, but it illustrated your
> point very well. The next time someone asks me if I'm interested in doubles, I can
> say "do you mean tennis or floating point arithmetic?"

vbg

Jerry