scwewy answerrs that don't belong

[SteveDB1]

Hi all.
For the past few months I've been developing, and working on a really large,
and complex worksheet.
Every so often I'll notice that one of my worksheet functions does not
return a simple answer, and when I go tracing it down by modifying the format
of the cell in question I'll notice that instead of returning a zero, or a
true answer-- in the case of my if functions-- it'll return a value that is
so insignificantly small as to be a zero.
E.g.,
If(a4=X,"ok",X-a4)
where X is some numeric value.
If the statement is true, it generally returns the "ok" as my true
statement. However, once in a while I'll get a number and E-16, or E-17,
etc.....
I know that the E to some number is the exponential, raising a number to a
specified power.
It's the issue of it returning that, instead of it giving a zero.
Is there any means by which to over-ride those, and get the "correct"
response?
Thank you.

 

[Noob Jedi]

Hi all.
For the past few months I've been developing, and working on a really large,
and complex worksheet.
Every so often I'll notice that one of my worksheet functions does not
return a simple answer, and when I go tracing it down by modifying the format
of the cell in question I'll notice that instead of returning a zero, or a
true answer-- in the case of my if functions-- it'll return a value that is
so insignificantly small as to be a zero.
E.g.,
If(a4=X,"ok",X-a4)
where X is some numeric value.
If the statement is true, it generally returns the "ok" as my true
statement. However, once in a while I'll get a number and E-16, or E-17,
etc.....
I know that the E to some number is the exponential, raising a number to a
specified power.
It's the issue of it returning that, instead of it giving a zero.
Is there any means by which to over-ride those, and get the "correct"
response?
Thank you.

I'm guessing your obstacle lies within the cell/column not wide enough
to display the full number. If you widened it manually or
autoformatted that column, then it would display the complete value.

 

[Noob Jedi]

I'm guessing your obstacle lies within the cell/column not wide enough
to display the full number. If you widened it manually or
autoformatted that column, then it would display the complete value.- Hidequoted text -

- Show quoted text -

I also forgot to mention another method. If you change the format of
the cell to a number, it give you the value you need.

 

[Sandy Mann]

You neeed to round the number before you test it. For example if you want
the number to be within three decimal places then use:

=If(ROUND(A4,3)=X,"ok",X-A4)

Or Round the figure in A4:

=ROUND(SUM(B2:B5),3)

then =If(A4=X,"ok",X-A4)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[joeu2004]

Every so often [....] I'll notice that instead of returning a zero, or a
true answer-- in the case of my if functions-- it'll return a value that
is so insignificantly small as to be a zero.
[....]
Is there any means by which to over-ride those, and get the "correct"
response?

This is a common problem, a consequence of how binary computers must
represent numbers, especially fractional numbers, but also extremely
large numbers. Fractional numbers are represented by the sum of
powers of 1/2. Consequently, most fractional numbers cannot be
represented exactly.

You can avoid anomalies to a large degree by taking the following
steps.

1. Use ROUND() liberally. There is no point in rounding constants.
But it is especially important to round division results, as well as
multiplication by fractional numbers. And it is often useful to round
multiplication in general, and even addition and subtraction.

2. Avoid unnecessary parentheses, especially around the last
operation; for example, =A1-A2 is better than =(A1-A2), unless you are
interested in capturing the potentially infinitessimal difference
between A1 and A2. In some cases, Excel tries to mask infinitessimal
differences by changing the result to exactly zero.

3. Avoid testing for equality when one or both operands are the result
of formulas. There are a number of work-arounds. For example,
instead of IF(A1=A2,....), you could try IF(ABS(A1-A2)<0.005,...).
Alternatively, IF(ROUND(A1,2)=ROUND(A2,2),...) or even
IF(TEXT(A1,"0.00")=TEXT(A2,"0.00"),...).

Note: In those simple examples, one choice might seem like the clear
winner over the alternatives. I am just demonstrating a number of
solutions, each of which is the "best" solution in some circumstances.

4. In appropriate cases, if you have a column of numbers that should
sum to a known total (e.g. the sum of probabilities or percentages of
the whole should sum to 100%), change the last entry in the column to
be, for example, 100% - SUM($A$1:A99), where A99 is the cell above and
$A$1 is the first cell of the column. But the operative word is
"appropriate". There are times when this will inadvertently mask
errors or slant results. Most professional people understand that the
sum of the parts does not always equal the whole exactly. It is not
uncommon to have a footnote that explains that the column sums to more
or less than 100% due to rounding errors.

But sometimes, the problems with binary arithmetic is inescapable.
Here is one interesting and seemingly inexplicable example that came
up in discussion recently ("Is it a bug?"). Compare the following
results:

=ROUND((2226 - 123.36 - 39.34 - 303-1600)*0.05, 2)

=ROUND(((2226 - 303 - 1600) - (123.36 + 39.34))*0.05, 2)

Mathematically, the expressions are identical, merely a rearrangement
of terms. For a complete explanation of why they differ, see the
thread "Is it a bug?".

 

[David Biddulph]

Wouldn't it be better to have
=If(ROUND(A4-X,3)=0,"ok",X-A4)
rather than
=If(ROUND(A4,3)=X,"ok",X-A4) ?

 

[Sandy Mann]

Yes of course, I was so intent on the ROUND() function I just accepted the
OP's formula without thinking it through.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[joeu2004]

Wouldn't it be better to have
=If(ROUND(A4-X,3)=0,"ok",X-A4)
 rather than
=If(ROUND(A4,3)=X,"ok",X-A4) ?

Good point, and probably right.

This might be especially true when comparing results on either side of
zero. Remember that ROUND() goes away from zero. So rounding each
operand on either side of zero might increase the distance, whereas
rounding their difference might be a truer result.

 

[SteveDB1]

Ok, thanks guys.
I've already tried those solutions. Sorry I didn't mention them in my post.
You're discussing rounding, and formatting. I'm not concerned about the
format, or rounding. To me that's a moot issue. I do appreciate your response
though.
From what I can gather, this appears to be a matter of Excel erroneously
"seeing" the number as something other than zero.
I have two identical equations in two different cells/columns An, and Bn.
I will ultimately be modifying the primary cell in column A, and want to
know how much the difference will be once I'm done.
For now the cell values are identical. I.e., Column A's equation is
identical to column B's equation.
Every cell on both columns-- with the exception of a single cell in column
b-- returns a true value.
What I'm trying to ascertain is why there is a discrepancy in only this one.
Then on a larger scale, why does this happen?
It only happens occasionally.
In the past I've used this general form to test if my cell values are within
a certain toleranced value, or if they match, and will, upon occasion get an
E-15 answer.

I'm interested in getting an "exact" match. If I don't get the match, I can
go back and analyze the data to determine if there's an error or not.
When it returns an E-14 or smaller, there's something else going on besides
a difference in the two cell's values. To me it seems to be a computing
caluculation error.
I'd like to know if there's anyway to resolve that.
Best regards.

 

[SteveDB1]

Thanks Joe.
I found a comparable article on that Excel bug you referenced, dating back
to May 10, 2005. Dana DeLouis describes an identical issue to the one I'm
having, and while I don't like the answer, it does answer my question.
I also tried to test one of the issues that JW Lewis quandried about in the
same article.
I.e., 1-1/3-2/3 and 1-2/3-1/3. I got an absolute zero on both of mine. I
tested them with an if statement.
So, thanks for your explanations.
I'll consider this resolved for this particular discussion's purposes.
Hopefully MS will actually resolve this one day.
It almost seems that the calculations taking place are getting skewed in the
background, and then returning an erronous answer. I.e., a computer issue.
OMG!!! the world is coming to an end.
I have to agree with Harlan's statement-- HY, we got to the moon and back
with a slide rule. Spooky to be sure.

Every so often [....] I'll notice that instead of returning a zero, or a
true answer-- in the case of my if functions-- it'll return a value that
is so insignificantly small as to be a zero.
[....]
Is there any means by which to over-ride those, and get the "correct"
response?

This is a common problem, a consequence of how binary computers must
represent numbers, especially fractional numbers, but also extremely
large numbers. Fractional numbers are represented by the sum of
powers of 1/2. Consequently, most fractional numbers cannot be
represented exactly.

You can avoid anomalies to a large degree by taking the following
steps.

1. Use ROUND() liberally. There is no point in rounding constants.
But it is especially important to round division results, as well as
multiplication by fractional numbers. And it is often useful to round
multiplication in general, and even addition and subtraction.

2. Avoid unnecessary parentheses, especially around the last
operation; for example, =A1-A2 is better than =(A1-A2), unless you are
interested in capturing the potentially infinitessimal difference
between A1 and A2. In some cases, Excel tries to mask infinitessimal
differences by changing the result to exactly zero.

3. Avoid testing for equality when one or both operands are the result
of formulas. There are a number of work-arounds. For example,
instead of IF(A1=A2,....), you could try IF(ABS(A1-A2)<0.005,...).
Alternatively, IF(ROUND(A1,2)=ROUND(A2,2),...) or even
IF(TEXT(A1,"0.00")=TEXT(A2,"0.00"),...).

Note: In those simple examples, one choice might seem like the clear
winner over the alternatives. I am just demonstrating a number of
solutions, each of which is the "best" solution in some circumstances.

4. In appropriate cases, if you have a column of numbers that should
sum to a known total (e.g. the sum of probabilities or percentages of
the whole should sum to 100%), change the last entry in the column to
be, for example, 100% - SUM($A$1:A99), where A99 is the cell above and
$A$1 is the first cell of the column. But the operative word is
"appropriate". There are times when this will inadvertently mask
errors or slant results. Most professional people understand that the
sum of the parts does not always equal the whole exactly. It is not
uncommon to have a footnote that explains that the column sums to more
or less than 100% due to rounding errors.

But sometimes, the problems with binary arithmetic is inescapable.
Here is one interesting and seemingly inexplicable example that came
up in discussion recently ("Is it a bug?"). Compare the following
results:

=ROUND((2226 - 123.36 - 39.34 - 303-1600)*0.05, 2)

=ROUND(((2226 - 303 - 1600) - (123.36 + 39.34))*0.05, 2)

Mathematically, the expressions are identical, merely a rearrangement
of terms. For a complete explanation of why they differ, see the
thread "Is it a bug?".

 

[joeu2004]

I found a comparable article on that Excel bug you referenced,
dating back to May 10, 2005.

Actually, I was referring to a recent thread entitled "Is it a bug?",
9 Dec 2007, not this thread entitled "Excel bug?". But these threads
come up once a month, perhaps more often, or so it seems. It is a
FAQ.

And while the subject of the thread asks if this is an Excel bug, I
want to emphasize that the answer is always "no". Although Excel does
try to ameliorate the effect (e.g. the "optimization" introduced in
Excel 97 to reduce infinitessimal results to zero), for the most part
it is simply the nature of binary arithmetic in computers that use a
finite number of bits (namely 64) to store a "floating-point"
representation.

Hopefully MS will actually resolve this one day.

For the most part, it is not MS's problem to resolve. I do believe
that much of the problem would be avoided if Excel used decimal
arithmetic, as Visicalc did originally. But as Jerry Lewis is quick
to point out: that will not solve all the problems. Division and
multiplication by (some) fractional numbers will still cause problems,
depending on the precision of the decimal representation, as will the
representation of fractional numbers with infinitely repeating
series. Then there still remains the issue of representing numbers
with a finite number of digits, which the IEEE 754 decimal floating-
point representation proposes.

In fact, it is interesting that a (finite) decimal representation
could introduce new anomalies. For example, if A1 is 1/3 and A2 is
3*A1, binary computer arithmetic returns exactly 1 in A2, as you would
like. This is not a gimmick of Excel; the same thing is true in VBA.
It has to do with how the binary implementation rounds the longer
internal result to 64 bits. (The exact binary representation before
internal rounding is actually infinitessimally less than 1.)

But an "exact" decimal representation might return 0.9...9. It
depends on the implementation. (I suspect an IEEE 754 decimal
floating-point implementation would still yield exactly 1. Intel does
provide a software library with one implementation of the proposed
standard. I don't have access to it.)

It almost seems that the calculations taking place are getting skewed in the
background, and then returning an erronous answer. I.e., a computer issue.

I take issue with the phrase "erroneous answer". It is a question of
finite v. infinite representation. Assuming that "infinite
representation" is unrealistic, there will also be numerical
"error" (difference) in any computation, whether it be by hand or by
computer. There is an entire branch of mathematics devoted to
studying this called Numerical Analysis. It predates the advent of
computers.

The issue is made more mysterious with the advent of computers because
human calculation usually try to "reduce terms" as much as possible
before it becomes necessary to actually compute a number. And because
humans do use a form of finite decimal representation that tends to
avoid computational error in some simple cases. It is my contention
that if computers mimicked the same finite decimal representation, no
one would squawk about the "error" in computation because it would be
the same as the error in human computation of the same problem -- or
at least the explanation would easily be understood in those terms.

 

[SteveDB1]

Joe, could you please provide a link to the discussion you're referencing?
I've backed out all the way to the generic Excel newsgroup, and cannot
locate it.
I even tried giving the date in mm/dd/yyyy format, and I found nothing.
Thanks.
My apologies for the "erroneous answer" statement. After I wrote it, and
posted, I realized that it wasn't going to sound as I intended.
I'm fully cognizant that computers don't make mistakes, or provide
"erroneous" feedback. They respond exactly as the input by the operator
demands.

I just find it odd that I only get the 2^-50 response occasionally, instead
of consistently. If it was consistently that answer, I'd understand, and
wouldn't grumble. Which is why I loosely used "erroneous." It's the one in
10000 occurrences that makes me wonder, and ask why, etc....

Thanks again for your explanation.

Best regards.

 

[Jerry W. Lewis]

Hopefully MS will actually resolve this one day.

Actually, this indicates that you have not understood what you have read.
For example, consider =12.22-12.2-0.02 which mathematically is zero, but
numerically returns 1.35E-15 in Excel and almost all other software. There
is nothing wrong with the math that is performed here, but none of the three
numbers involved have exact binary representations, and hence must be
approximated. The decimal representations of the best 53-bit binary
approximations (dictated by the IEEE 754 standard that is followed by almost
all software and hardware) to these three numbers are
12.2200000000000006394884621840901672840118408203125
-12.199999999999999289457264239899814128875732421875
-0.0200000000000000011102230246251565404236316680908203125
If you do the math, you will see that the answer of 1.35E-15 is actually
correct, given the unavoidable initial approximations, and not "skewed" in
any way. It is because the math is correct that rounding is the appropriate
solution, but the appropriate level of rounding depends on the particular
calculation, and hence is not something that MS can do for you.

I also tried to test one of the issues that JW Lewis quandried about in the
same article.
I.e., 1-1/3-2/3 and 1-2/3-1/3. I got an absolute zero on both of mine. I

As you should. If you read that post more carefully, you will see that my
example applied to a hypothetical decimal computer, not to Excel. I was
illustrating the point that even if computers switched from binary to decimal
they would still not be able to eliminate this issue. It is not
mathematically possible to completely eliminate such issues as because they
are a consequence of finite precision, not merely a particular number base
system.

Jerry

 

[joeu2004]

Joe, could you please provide a link to the discussion you're referencing?
http://groups.google.com/group/micr...72f2f000ce7/09766cf29dee2ef2#09766cf29dee2ef2


I just find it odd that I only get the 2^-50 response occasionally, instead
of consistently. If it was consistently that answer, I'd understand, and
wouldn't grumble. Which is why I loosely used "erroneous." It's the one in
10000 occurrences that makes me wonder, and ask why, etc....

Only one in 10000 times!? I would say you truly live a charmed life.

Well, to get exactly a difference of 2^-50 so rarely might not be
odd. I mean it is odd that you encountered some kind of anomaly so
infrequently.

Examples are not difficult to create. Compare the results of 100 -
99.99 and 0.01 or even 1/100. You will find that 0.01 and 1/100 are
exactly equal (even if A1 is 100 and A2 is 1/A1), but neither is
exactly equal ot 100 - 99.99. The explanation is "obvious" when you
look at the internal representation. The result of 0.01 and 1/00 is
exactly 0.01000000000000000020816681711721685132943093776702880859375,
whereas the result of 100 - 99.99 is exactly
0.0100000000000051159076974727213382720947265625. The is not wrong
when you consider that 99.99 is represented exactly by
99.9899999999999948840923025272786617279052734375. On the other hand,
ROUND(100 - 99.99, 2) does exactly match 0.01; that is, the internal
representations match.

 

[SteveDB1]

Jerry,
Thanks for your involvement in this discussion.
Your explanation then brings up another question in my mind....
you stated that there are "approximated" answers that go out to some 20 or
more decimal places because there is no exact binary representative answer
available.

Would that then be the cause of this "anomaly" that I've "found?"

And to both Joe, and Jerry-- please forgive my inaccurate use of
language/semantics, and syntax here, and in my previous posts on this topic.
While I'm familiar with how computers operate, and can get my way around
them, and can even write a little code, I'm by no means a computer expert--
especially when it comes to the details in the background. I suppose I could
be classified as being one of those guys who knows just enough on how to get
into trouble, but not enough to get out of it. I only ever mastered one craft
in my life, and my health stopped me from practicing that one over a decade
ago; hence my entrance into the world of computers (shortly thereafter) to
begin with.

and to Joe-- I can't say that it's exactly 1 in 10000. It could be more like
1 in 20. I just never paid much attention to it until this one because over
the past 8 months, it's only happened maybe 8 to 10 times (and while it was
indeed frustrating, I just found ways to work around it, or to deal with it
to my satisfaction). But this last time made it stick out in my mind, and
since I use the if worksheet function so frequently (on every worksheet in
this book --around 60 or so-- and a multitude of others in other workbooks),
it stuck, and my patience for it finally ran out.
Oh, and my previous ones that arose were not exactly the 2^-50 answer
either. Moreover, the opportunity to go back and test to see what the others
were is now long gone, as the last workbook that I'm specifically thinking
of, I removed the specific worksheets once the analysis was completed-- to
make sure that others who would be working on it in the future could not be
confused by the analysis that I'd done. I.e., they'd have to spend far more
time than would be necessary to grasp what I'd done to solve the initial
problems faced with that data set, so I only saved the final data, and
deleted all of the analysis workups.
I will however from this point on do an analysis of the infinitesimal values
that I get by using =log(cell#,2) to determine if it's a binary
representation of that cell's value.
So, again thank you both for your comprehensive explanations, and helping me
to better understand how the mathematics in computing works.

Best regards to you both.

 

[joeu2004]

you stated that there are "approximated" answers that go out to some 20 or
more decimal places because there is no exact binary representative answer
available. Would that then be the cause of this "anomaly" that I've "found?"

Yes! That is what we have both been trying to explain and Sandy
implied.

And it is not just that there might be no exact binary representation
of the answer per se, but also there might be no exact representation
of the operands. Moreover, the relative scale of pairwise operands
can also be a factor, which is why (12.22-0.02-12.2) does not exactly
equal (12.22-12.2-0.02), which might seem to conflict with Jerry's
simplistic explanation based on the individual exact binary
representations alone.

(Jerry's explanation works because in the order that he chose, each
set of pairwise operands has the same binary magnitude, and the result
of the operation fits exactly within the 64-bit floating point
representation. By the way, the "superfluous" parentheses are
important in my examples above. Without them, Excel actual "corrects"
12.22-0.02-12.2 to exactly zero because in that case, the
infinitessimal difference is within its threshold.)

I will however from this point on do an analysis of the infinitesimal
values that I get by using =log(cell#,2) to determine if it's a binary
representation of that cell's value.

I'm not sure how LOG(...,2) would be useful to determine if the
problem is with the binary representation. Why can't you just accept
the fact that it is?

Even if LOG(...,2) were implemented to look at the binary
representation (I doubt that it is; it probably uses an approximation
formula), the infinitessimal difference is rarely a single bit.
LOG(12.22-0.02-12.2,2) is -49, but LOG(12.22-12.2-0.02) is about
-49.396. What more does that tell you than we have already
explained? And how does that (especially -49.396) tell you that the
problem is indeed due to binary representation?

So, again thank you both for your comprehensive explanations

BTW, one work-around that I omitted from my list (on purpose) is to
set Tools>Options>Calculation to "Precision as displayed". Although
that might be a viable solution for you (you must decide), I eschew it
as a general solution because it seems dangerous to me.

 

[Jerry W. Lewis]

Jerry,
Thanks for your involvement in this discussion.

you're welcome

I will however from this point on do an analysis of the infinitesimal values
that I get by using =log(cell#,2) to determine if it's a binary
representation of that cell's value.

The VBA functions I posted at
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5
would probably be more helpful for your purpose.

Jerry