IF(AND( Formula gives same answer all the time

[Overheard]

Hey guys, I've currently got this formula and was wondering why it isn't working... It always comes up with "Continue", where it should come up as "Needs Attention".

=IF(AND(MAX(D4:D6)-MEDIAN(D4:D6)=MEDIAN(D4:D6)-MIN(D4:D6), MAX(D4:D6)-MIN(D4:D6)<=0.2), "Needs Attention", "Continue")

Where:
D4 = 0.56
D5 = 0.50
D6 = 0.44

From whatever troubleshooting I've done, it seems like the problem lies in logical1 of the AND statement.

Cheers!

[boopathiraja]

I don't get the logic but may be this

Please Login or Register  to view this content.

[Kaper]

No Booşathì , this is (quite) common problem of double precission accuracy.
Have a look on result of (make sure formatting is general):

Please Login or Register  to view this content.

it is not 0 but some very small number 5.55112E-17

@Overheard: try this:

Please Login or Register  to view this content.

and see http://support2.microsoft.com/kb/78113 for additional info.

[Overheard]

Hey guys, thanks for the help but it's not quite what I'm after. This statement is a part of a much larger nest of if statements, and I'm trying to see if there difference between the max and median, and the median and min is equal. I also have to make sure that if that difference is equal, that it is less than 0.2.

The statements that you both have given me are not seeing if they are equal, but rather taking one from the other. If you can think of anything else that may help that would be awesome!

Cheers.

[Bob Macregor]

If they are taking one from the other, then isn't absolute value of that difference<=0.2 what you want?

[ben_hensel]

The statements that you both have given me are not seeing if they are equal, but rather taking one from the other.

Kaper's solution is checking whether the difference is small enough to ignore as rounding error, so isn't that perfectly substitutable with what you want?

[daddylonglegs]

The normal solution is to use ROUND, e.g.

=IF(AND(ROUND(MAX(D4:D6)-MEDIAN(D4:D6),9)=ROUND(MEDIAN(D4:D6)-MIN(D4:D6),9), MAX(D4:D6)-MIN(D4:D6)<=0.2), "Needs Attention", "Continue")

rounding to 9 decimal places shouldn't lose you any accuracy if your figures really only have 2 decimal places