software

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 gt; Formula Auditing gt; 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
See www.cpearson.com/excel/rounding.htm

quot;M and Dquot; wrote:

gt; I've got an unusual problem with the ABS (absolute value) function in Excel 2002 SP 3 running on Windows XP Home SP 2.
gt;
gt; 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.
gt;
gt; 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.
gt;
gt; From Tools gt; Formula Auditing gt; Evaluate Formula I made this startling discovery:
gt;
gt; 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)!
gt;
gt; What the...where did -1.81898940354586E-12 come from? Have I done something wrong?
gt;
gt; Steven
gt;
gt;
gt;

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
quot;JMBquot; gt; wrote in message ...
gt; See www.cpearson.com/excel/rounding.htm
gt;
gt; quot;M and Dquot; wrote:
gt;
gt;gt; I've got an unusual problem with the ABS (absolute value) function in Excel 2002 SP 3 running on Windows XP Home SP 2.
gt;gt;
gt;gt; 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.
gt;gt;
gt;gt; 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.
gt;gt;
gt;gt; From Tools gt; Formula Auditing gt; Evaluate Formula I made this startling discovery:
gt;gt;
gt;gt; 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)!
gt;gt;
gt;gt; What the...where did -1.81898940354586E-12 come from? Have I done something wrong?
gt;gt;
gt;gt; Steven
gt;gt;
gt;gt;
gt;gt;

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

quot;M and Dquot; gt; wrote in message
...
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
quot;JMBquot; gt; wrote in message
...
gt; See www.cpearson.com/excel/rounding.htm
gt;
gt; quot;M and Dquot; wrote:
gt;
gt;gt; I've got an unusual problem with the ABS (absolute value) function in
Excel 2002 SP 3 running on Windows XP Home SP 2.
gt;gt;
gt;gt; 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.
gt;gt;
gt;gt; 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.
gt;gt;
gt;gt; From Tools gt; Formula Auditing gt; Evaluate Formula I made this startling
discovery:
gt;gt;
gt;gt; 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)!
gt;gt;
gt;gt; What the...where did -1.81898940354586E-12 come from? Have I done
something wrong?
gt;gt;
gt;gt; Steven
gt;gt;
gt;gt;
gt;gt;
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
groups.google.com/group/micro...06871cf92f8465
to be useful.

Thanks for the plug, Bob.

Jerry

quot;M and Dquot; wrote:

gt; Thank you for that (and for reading my post).
gt;
gt; While I understand and accept the point the article is making, I'm afraid I don't see how it applies to my situation.
gt;
gt; First, all the arithmatic throughout my spreadsheet is adding or subtracting, so I don't see where there could be an issue with rounding.
gt;
gt; Second, other cells in my worksheet that perform the identical calculation, albeit on different ranges of cells, produce the correct result.
gt;
gt; 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.
gt;
gt; 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'.)
gt;
gt; I'm going to sleep now, so please don't be offended if I don't write again for a while.
gt;
gt; Steven
gt;
gt;
gt;
gt; quot;JMBquot; gt; wrote in message ...
gt; gt; See www.cpearson.com/excel/rounding.htm
gt; gt;
gt; gt; quot;M and Dquot; wrote:
gt; gt;
gt; gt;gt; I've got an unusual problem with the ABS (absolute value) function in Excel 2002 SP 3 running on Windows XP Home SP 2.
gt; gt;gt;
gt; gt;gt; 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.
gt; gt;gt;
gt; gt;gt; 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.
gt; gt;gt;
gt; gt;gt; From Tools gt; Formula Auditing gt; Evaluate Formula I made this startling discovery:
gt; gt;gt;
gt; gt;gt; 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)!
gt; gt;gt;
gt; gt;gt; What the...where did -1.81898940354586E-12 come from? Have I done something wrong?
gt; gt;gt;
gt; gt;gt; Steven
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;

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 quot;problemquot;.

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 quot;do you mean tennis or floating point arithmetic?quot;

Steven

quot;Jerry W. Lewisquot; gt; wrote in message news
gt; It applies to your situation, because most decimal fractions are
gt; nonterminating binary fractions that must be approximated. When you do math
gt; with approximate inputs, naturally the output will only be approximate.
gt;
gt; If you enter 12182.23, you actually get
gt; 12182.22999999999956344254314899444580078125. You cannot directly see this
gt; value, because Excel will display no more than 15 digits, but you can detect
gt; it by subtraction. There are in fact 55 distinct numbers that Excel can
gt; represent which all display as 12182.2300000000, yet can differ from each
gt; other by as much as 1.00044417195022E-10.
gt;
gt; What has happened to you is the binary equivalent of
gt; ((1/3) (1/3))-(2/3) = 0.3333 0.3333-0.6667 = -0.0001
gt; The decimal math is right, but the answer is not zero because of the initial
gt; decimal approximations to non-terminating decimal fractions.
gt;
gt; If you are just adding and subtracting 2 decimal place numbers, then
gt; rounding calculation results to 2 decimal places (especially for comparisons
gt; to specific values or other calculations) will do no violence to your intent
gt; and will avoid surprises like this.
gt;
gt; Alternately, you could avoid the issue altogether by doing your accounting
gt; in pennies instead of dollars; integers are exactly representable.
gt;
gt; If you want to learn more about the internal representation of numbers, you
gt; might find my VBA functions at
gt; groups.google.com/group/micro...06871cf92f8465
gt; to be useful.
gt;
gt; Thanks for the plug, Bob.
gt;
gt; Jerry
gt;
gt; quot;M and Dquot; wrote:
gt;
gt;gt; Thank you for that (and for reading my post).
gt;gt;
gt;gt; While I understand and accept the point the article is making, I'm afraid I don't see how it applies to my situation.
gt;gt;
gt;gt; First, all the arithmatic throughout my spreadsheet is adding or subtracting, so I don't see where there could be an issue with rounding.
gt;gt;
gt;gt; Second, other cells in my worksheet that perform the identical calculation, albeit on different ranges of cells, produce the correct result.
gt;gt;
gt;gt; 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.
gt;gt;
gt;gt; 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'.)
gt;gt;
gt;gt; I'm going to sleep now, so please don't be offended if I don't write again for a while.
gt;gt;
gt;gt; Steven
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;JMBquot; gt; wrote in message ...
gt;gt; gt; See www.cpearson.com/excel/rounding.htm
gt;gt; gt;
gt;gt; gt; quot;M and Dquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; I've got an unusual problem with the ABS (absolute value) function in Excel 2002 SP 3 running on Windows XP Home SP 2.
gt;gt; gt;gt;
gt;gt; gt;gt; 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.
gt;gt; gt;gt;
gt;gt; gt;gt; 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.
gt;gt; gt;gt;
gt;gt; gt;gt; From Tools gt; Formula Auditing gt; Evaluate Formula I made this startling discovery:
gt;gt; gt;gt;
gt;gt; gt;gt; 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)!
gt;gt; gt;gt;
gt;gt; gt;gt; What the...where did -1.81898940354586E-12 come from? Have I done something wrong?
gt;gt; gt;gt;
gt;gt; gt;gt; Steven
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;

quot;M and Dquot; wrote:

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

Glad it helped

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

vbg

Jerry