Read Chip Pearson's article about floating point math

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


"trucat" wrote:

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