# IF function giving wrong result

1. ## IF function giving wrong result

hi, wrote an IF function and it seems to be giving the wrong results, and i need help troubleshooting.

i have these figures in the following cells:
A1: 220
B1: 220
C1: 0
D1: 0.83
E1: 175.5
F1: 7.1

basically i'm trying to verify that F1 is equal to a formula involving A1 to E1.

and it keeps returning 'wrong', even though the results match.

sigh. i've been wrecking my brains over this for two days. any advice would be much appreciated!

-kenneth

2. Mathematically the result is not matching acurately. The left hand side evaluates to 7.099999 and not 7.1 and hence the behaviour you are observing.

You may change your function to:
=IF(ROUND(((A1*D1)-((C1*E1)/B1)-E1),4)=F1,"correct","wrong")

Note that here I am rounding the LHS result to 4 decimals, which you may vary, and then comparing it with 7.1

- Mangesh

3. thanks, but why do you say that mathematically it's 7.099999?

4. Try entering just
=(A1*D1)-((C1*E1)/B1)-E1
in a cell and check out the result for yourself.

- Mangesh

5. i tried that and still got 7.1. and mathematically it shouldn't be 7.09999999 because no portion of that formula should create that many decimal places...

6. Hi!

Excel works with binary approximations to decimal fractions, much as decimal numbers work with approximations to fractional numbers. ( Think of 1/3 : exact. Think of 0.33333.........:never exact)

Excel also works only to 15 significant figures.

Within these two constraints lies your problem.

There are workarounds such as if abs(a-b)<0.00000001 then do something. This acknowledges that there may be a whisker of difference between the numbers which doesn't matter.

If it is critical that every digit must count, you could try adjusting your numbers so that you are dealing entirely with integers (again, subject to the 15 sig. fig. limit) or get a program which goes beyond 15 sig. fig. accuracy

Alf

7. thanks much for the explanation! very good tip to keep in mind from now on. you folks are swell!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1