IF Statement Returns False When True On Certain Numbers

**paulm99** · 11-26-2013, 04:01 PM

First time post!

I am subtracting one cell from another and putting the answer in a separate cell. Then using the answer in another I use a formula to see if the answer equals a certain value.

Eg A3-A1. Answer goes in B1. Then in C1 formula IF(B1=0.2,"TRUE","FALSE")

For some reason certain values do not work and I am completely lost for why this is happening! I use excel regularly but am no expert but this has completely stumped me. I've a horrible feeling I may look very stupid over this! Ha.

I have attached an excel sheet in case I have not explained this very well! All three examples should show TRUE but only the last one is!!

Thanks in advance!

Paul

**Ron Coderre** · 11-26-2013, 04:07 PM

Binary math strikes again...
• Select cell A2
• Press F2...Press F9
The result in the formula bar will be: 0.199999999999999

Computers work with binary representations of numbers, but humans work with decimal numbers. There are some decimal numbers that cannot be exactly represented in binary. It's the same kind of issue that prevents the fraction 1/3 from being exactly represented as a decimal number. 1/3 as a decimal is 0.333333333(ad infinitum)

Try this:
A3: =IF(ROUND(A2,6)=0.2,"TRUE","FALSE")

Inelegant...but accurate.

Does that help?

**Olly** · 11-26-2013, 04:09 PM

It's a floating point arithmetic error.

Change your formula in A3 to:

Formula:

Please Login or Register  to view this content.

**paulm99** · 11-26-2013, 04:41 PM

Brilliant. The ROUND seems to have fixed it! I've got a feeling I might be on here more often!

Many thanks for the quick responses guys!!

IF Statement Returns False When True On Certain Numbers

LinkBack

Thread Tools

Rate This Thread

Display

IF Statement Returns False When True On Certain Numbers

Re: IF Statement Returns False When True On Certain Numbers

Re: IF Statement Returns False When True On Certain Numbers

Re: IF Statement Returns False When True On Certain Numbers

Thread Information

Users Browsing this Thread

Similar Threads

lookup exact match returns TRUE or FALSE

IF STATEMENT returns False, even though the results are true

An If statement where the Logical test is true, but returns the False result in the sheet

InStr function returns pseudo True/False?

[SOLVED] Function that compares dates and returns TRUE or FALSE

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions