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

 

[Guest]

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

 

[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.

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

 

[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)

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

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

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.my worksheet was still alerting me that the cell was something other than
zero.does NOT evaluate to ABS(0). Instead, it evaluates to
ABS(-1.81898940354586E-12)!

 

[Guest]

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/microsoft.public.excel/msg/b106871cf92f8465
to be useful.

Thanks for the plug, Bob.

Jerry

 

[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

 

[Guest]

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