Excel Error....or Something Else?

[Demo8II]

Here is what happens.

Example 1:

<table x:str="" style="border-collapse: collapse; width: 169pt;" width="225" border="0" cellpadding="0" cellspacing="0"><col style="width: 169pt;" width="225"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 169pt;" x:num="81.766740000000098" width="225" align="right" height="17">81.76674000000010000000000000000000</td> </tr></table>+
<table x:str="" style="border-collapse: collapse; width: 169pt;" width="225" border="0" cellpadding="0" cellspacing="0"><col style="width: 169pt;" width="225"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 169pt;" x:num="297.21221999999995" width="225" align="right" height="17">297.21222000000000000000000000000000</td> </tr></table>=
<table x:str="" style="border-collapse: collapse; width: 169pt;" width="225" border="0" cellpadding="0" cellspacing="0"><col style="width: 169pt;" width="225"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 169pt;" x:num="378.97896000000003" width="225" align="right" height="17">378.97896000000000000000000000000000</td> </tr></table>

Example 2:

<table x:str="" style="border-collapse: collapse; width: 169pt;" width="225" border="0" cellpadding="0" cellspacing="0"><col style="width: 169pt;" width="225"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 169pt;" x:num="81.766739999999999" width="225" align="right" height="17">81.76674000000000000000000000000000</td> </tr></table>+<table x:str="" style="border-collapse: collapse; width: 169pt;" width="225" border="0" cellpadding="0" cellspacing="0"><col style="width: 169pt;" width="225"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 169pt;" x:num="297.21221999999995" width="225" align="right" height="17">297.21222000000000000000000000000000</td> </tr></table>=
<table x:str="" style="border-collapse: collapse; width: 169pt;" width="225" border="0" cellpadding="0" cellspacing="0"><col style="width: 169pt;" width="225"><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 169pt;" x:num="378.97895999999992" width="225" align="right" height="17">378.97896000000000000000000000000000</td> </tr></table>

So, if you notice the difference between the first number in each equation, the "81." The first "81" has a "1" about 13 decimals into it and the second "81" does not.

When this "81" is added to "297" which is the same in each equation, you get the same answer each time "378."

When I run an if statement asking if the numbers are the same, I get a yes for both "297" and "378" but not for the "81."


So what is the deal here? At work we are putting a conditional format on these numbers to make sure they equal, and the conditional format is saying they are different even though they equal the same things. It is very frustrating.

If anyone knows why this happens I would be very appreciative. I am totally stumped. We are running microsoft xp with excel 2003 here at work.


Thanks,

Demo8II

 

[dave3009]

If I understand you right, Excel will only deal with 15 significant digits

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

 

[Demo8II]

But then why does the if formula tell me that the two "81" numbers are different from eachother?

 

[dave3009]

The two 81's are different as the final 1 is 13 digits from the decimal, when it is added to the larger number this 1 is dropped to accomodate the 15 digit limit hence your overall answers are deemed equal

 

[Demo8II]

Wow. You are quite brilliant.


Thanks so much, this was killing me!

 

[dave3009]

Thanks, glad to have helped