XL Inserts uncontrolled var, throwing off logic, How2StopIt?

[Legacy 36500]

I think I found a bug, or some feature gone awry.
Let me paste a formula evaluation to show what's wrong.
=IF(FS1961+HY8>=HY2,IF(FS1961+HY8<=HY3,FS1961+HY8,0),0)
=IF(2.2689+0.0001>=HY2,IF(FS1961+HY8<=HY3,FS1961+HY8,0),0)
=IF(2.26900000000001>=2.2667,IF(FS1961+HY8<=HY3,FS1961+HY8,0),0)
=IF(2.26900000000001>=2.2667,IF(2.26900000000001<=2.269,2.26900000000001,0),0)

So, to translate the math and logistics quickly:
=IF(FS1961+HY8>=HY2,IF(FS1961+HY8<=HY3,FS1961+HY8,0),0)
=IF(2.26900000000001>=2.2667 {True},IF(2.26900000000001<=2.269{FALSE},2.26900000000001,0),0)

How do I stop excel from inserting .00000000000001 on a this simple math equation?

Before you tell me to correct it in the cell, the cells are referencing, meaning the variables, and therefore the condition, won't always be present in that particular location (it's all dynamic), and there are a LOT of these checks.

I also had checked prior cells and it appears right on this location, when the # rolls over and truncates the 0's.
Formatting the cells as numbers didn't help.

 

[Legacy 36500]

Re: XL Inserts uncontrolled var, throwing off logic, How2Sto

I'd also like to change the color of another cell based on the condition of a specific cell.
Example:
Cell A1 = FALSE
Cell B1 Turns Red

I looked at conditional formatting, but I don't know enough of it to use it right.

 

[Damon Ostrander]

Re: XL Inserts uncontrolled var, throwing off logic, How2Sto

Hi PlNG,

Actually, this is not a bug in Excel. It is just a natural consequence of floating point math--and this is done by the math processor inside your computer's microprocessor chip, not Excel. The underlying problem is that 2.2689 + 0.0001 (or 2.2690) cannot be exactly represented in binary arithmetic, which is the way all numbers are represented internally in your computer in order for the processor to do math operations on them. As a result the processor yields a binary fraction as close to 2.2690 as it can to 64 bits precision, and the result is 2.26900000000001. This actually happens all the time in calculations, but is not usually noticed since we do not ordinarily display results to 15 digits (the last digit is usually the only one affected), and 14-digit accuracy is sufficient for 99.9999% of the types of work that people use computers for, including calculating interplanetary spacecraft trajectories.

But one consequence of this is that you should never test a computed number against another number for exact equality. If you want to know if two numbers are for all practical purposes equal, you should check to see if they are within some tolerance (e.g., are they the same to 10 significant digits?). For example your FS1961+HY8>=HY2 test would better be

FS1961+HY8>=HY2-1.E-12

When you want to test for equality, such as

FS1961+HY8=HY2

you should use something like

ABS(FS1961+HY8-HY2) < 1.E-12

I hope this helps.

Damon