XL Inserts uncontrolled var, throwing off logic, How2StopIt?

L

Legacy 36500

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

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
L

Legacy 36500

Guest
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. :oops:
 

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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
 

Forum statistics

Threads
1,147,846
Messages
5,743,518
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top