Is this a known excel error?? Sequential drag down & IF ERRORS??

[Guest]

I have several errors that occurs in both excel 2000 and excel 2003

the foundation
.. cells from b2 to g2 are formatted in green and with 2 digit number
formats with neg numbers in brackets. H2 is formatted in yellow and contains
the formula, and also the same number format.

problem #1.
in cell f2 for example, I will put in a number 150.00, then in cell
f3 the new number is 150.01. I then will highlight the two numbers and drag
down to say 148.00. The program will properly sequence the numbers up (or
down for that matter) in the two digit readout format properly. What really
takes the cake is in the formula box. The numbers are not consistantly in
whole integers. Sometimes I have whole integers, but on the whole I am
getting numbers that has anywhere from 9 to 12 zeros after with a 2 or a 7
attached to the end. eg. will be to see this number 150.00
151.0100000000002 151.020000000000002 and some attach a lot of 9's after.
Now when I highlight two cells of 149.00 and drag down the intergers are in
two decimal places .

Problem #2
I am doing a simple checkbook style program with two negative
entry columns for business , one column for being paid the day of service.
The second column for any transaction after that date, similar to accounts
receivables.. My twist on this was a math checker in the last column to
check our math, if the numbers are correct it will say "match".
The formula! In cell H2 enter
=if(g2+c2-d2-e2=f2,"match",g2+c2-d2-f2).

So, I enter the number 149.00 in cell e2, then 0 in f2, and 149 in g2 I get
"match". So, far so good. Now, I put in 149.00in e3, and .01 in f3 and
149.01 in g3 and I get a red (0.00). the next penny up will give me a
black (0.00)., and so on but some numbers for example such as 2.12, will give
me a "match" . I found that retyping the numbers that have long decimal
places such as 151.580000000002 to 151.58 will give me a (0.00). and
151.590000000002 into 151.59 will return "match".

 

[Guest]

Excel (like almost all software) does binary math, not decimal math. Most
terminating decimal fractions (including 0.01) are non-terminating binary
fractions that must be approximated (just as 1/3 must be approximated in
decimal). Excel does correct math on these approximate inputs, and naturally
arrives at an answer that is only approximately correct.

The usual approach in these situations is to round all results to to 2
decimal places, to synch up the approximations to different calculations.

For problem #2, approximations that are slightly larger or smaller than your
intended number will tend to balance out, keeping the overall level of
approximation small.

For problem #1, 150.01 gets approximated as
1.50009999999999990905052982270717620849609375
so every term is calculated using an increment that is a little smaller than
you intended. The result is that the degree of under-approximation increases
with the number of cells. An alternative to rounding each term would be to
avoid the approximation until the end in each cell, with a formula like
=(15000-2+ROW())/100

You might find the VBA functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to be useful if you want to learn more about this topic.

Jerry

 

[Guest]

Read Chip Pearson's article about floating point math

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

Thank you Duke
Good article

 

[Guest]

Excel (like almost all software) does binary math, not decimal math. Most
terminating decimal fractions (including 0.01) are non-terminating binary
fractions that must be approximated (just as 1/3 must be approximated in
decimal). Excel does correct math on these approximate inputs, and naturally
arrives at an answer that is only approximately correct.

The usual approach in these situations is to round all results to to 2
decimal places, to synch up the approximations to different calculations.

For problem #2, approximations that are slightly larger or smaller than your
intended number will tend to balance out, keeping the overall level of
approximation small.

For problem #1, 150.01 gets approximated as
1.50009999999999990905052982270717620849609375
so every term is calculated using an increment that is a little smaller than
you intended. The result is that the degree of under-approximation increases
with the number of cells. An alternative to rounding each term would be to
avoid the approximation until the end in each cell, with a formula like
=(15000-2+ROW())/100

You might find the VBA functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to be useful if you want to learn more about this topic.

Jerry

Thank you Jerry,

Wish excel had put in a fix for this,
it makes an added problem to repair, whenever an unequal match occurs when
it is really equal and a match in our formula.

 

[Guest]

Thank you Jerry,

You're welcome.

Wish excel had put in a fix for this,

My point was that a general fix is not possible because the available
precision will still be finite. To put it in the decimal arena where
hopefully your intuition is better, what should be the fix for the fact that
0.3333 is not 1/3? If software were to assume that whenever 0.3333 is
encountered, that 1/3 was intended, wouldn't that cause problems when it
wasn't intended? Moreover in some calculations you would need to consider
0.333 or even 0.33 to be equivalent to 1/3; where would you stop? The best
solution is an informed user base who can then take the most appropriate
action for their particular circumstance.

In Excel 97, MS did try to "fix" it in that fashion
http://support.microsoft.com/kb/78113
but IMHO it causes more confusion than it avoids.

Jerry

 

[Guest]

Thanks again.

I'm back to playing beginner excel programming

trucat