[KerriL]

Hello,

I am hoping someone can help me with this. Essentially, I am trying to construct an If statement that will return "OK" or "NO" based on whether one cell is equal to any of another 5. All 6 cells in question are populated via a VLOOKUP function or a calculated formula. All the cells themselves are populated correctly. However, the IF statement is not able to correctly determine whether the one cell matches one of the other five. To put it another way, if M1 is equal to H1, I1, J1, K1, or L1, I want the formula to display "OK", and if M1 does not equal any of those, I want it to display "NO". Sometimes the equation works, but 99% of the time it doesn't. I think it has something to do with how the Vlookup/other formulas are formatting the numbers in the cells that the IF statement is checking--but I can't figure out how. I've told all the cells to format as number decimals with two digits--and the rows where the If statement works seem to be formatted the same as the rows where it doesn't.

I've tried two different IF statements, and both have the same issue. Is something wrong with my formulas, or is this a formating issue?

=IF(OR(M697=H697,M697=I697,M697=J697,M697=K697,M69 7=L697),"OK","NO")

=IF(M693=H693,"OK",IF(M693=I693,"OK",IF(M693=J693, "OK",IF(M693=K693,"OK",IF(M693=L693,"OK","NO") ))))

Thank you very much for your help.

[Spencer101]

Originally Posted by KerriL

Hello,

I am hoping someone can help me with this. Essentially, I am trying to construct an If statement that will return "OK" or "NO" based on whether one cell is equal to any of another 5. All 6 cells in question are populated via a VLOOKUP function or a calculated formula. All the cells themselves are populated correctly. However, the IF statement is not able to correctly determine whether the one cell matches one of the other five. To put it another way, if M1 is equal to H1, I1, J1, K1, or L1, I want the formula to display "OK", and if M1 does not equal any of those, I want it to display "NO". Sometimes the equation works, but 99% of the time it doesn't. I think it has something to do with how the Vlookup/other formulas are formatting the numbers in the cells that the IF statement is checking--but I can't figure out how. I've told all the cells to format as number decimals with two digits--and the rows where the If statement works seem to be formatted the same as the rows where it doesn't.

I've tried two different IF statements, and both have the same issue. Is something wrong with my formulas, or is this a formating issue?

=IF(OR(M697=H697,M697=I697,M697=J697,M697=K697,M69 7=L697),"OK","NO")

=IF(M693=H693,"OK",IF(M693=I693,"OK",IF(M693=J693, "OK",IF(M693=K693,"OK",IF(M693=L693,"OK","NO") ))))

Thank you very much for your help.

=IF(COUNTIF(H693:L693,M693)0,"OK","NO")

[KerriL]

Originally Posted by Spencer101

=IF(COUNTIF(H693:L693,M693)0,"OK","NO")

Hello Spencer101,

Thank you. I tried your formula in my sheet where I'm having the issue, but it did not work. I tried it in a test sheet where I typed in the cell contents, and it did work. I think the problem is still that the cells which are populated by formulas are somehow not formated correctly. I think it might be that the cells contain the number out to four decimals, but the number the IF statement is searching for only has two. I'm not sure how to fix this--I know I can format it to only show two decimals, and I can use the ROUND formula to round an individual cell to two decimals, but how do I apply this to hundreds of cells? Thank you again for your help.

[Spencer101]

Originally Posted by KerriL

Hello Spencer101,

Thank you. I tried your formula in my sheet where I'm having the issue, but it did not work. I tried it in a test sheet where I typed in the cell contents, and it did work. I think the problem is still that the cells which are populated by formulas are somehow not formated correctly. I think it might be that the cells contain the number out to four decimals, but the number the IF statement is searching for only has two. I'm not sure how to fix this--I know I can format it to only show two decimals, and I can use the ROUND formula to round an individual cell to two decimals, but how do I apply this to hundreds of cells? Thank you again for your help.

An chance you could post a sample workbook?
Either here or email me - pubnut @ gmail.com

You can add =ROUND() to the formulas that populate columns H to L, that way you could limit those to the same number of decimal places.

[Ron Rosenfeld[_2_]]

On Fri, 23 Nov 2012 16:24:03 +0000, KerriL wrote:


Hello,

I am hoping someone can help me with this. Essentially, I am trying to
construct an If statement that will return "OK" or "NO" based on whether
one cell is equal to any of another 5. All 6 cells in question are
populated via a VLOOKUP function or a calculated formula. All the cells
themselves are populated correctly. However, the IF statement is not
able to correctly determine whether the one cell matches one of the
other five. To put it another way, if M1 is equal to H1, I1, J1, K1, or
L1, I want the formula to display "OK", and if M1 does not equal any of
those, I want it to display "NO". Sometimes the equation works, but 99%
of the time it doesn't. I think it has something to do with how the
Vlookup/other formulas are formatting the numbers in the cells that the
IF statement is checking--but I can't figure out how. I've told all the
cells to format as number decimals with two digits--and the rows where
the If statement works seem to be formatted the same as the rows where
it doesn't.

I've tried two different IF statements, and both have the same issue. Is
something wrong with my formulas, or is this a formating issue?

=IF(OR(M697=H697,M697=I697,M697=J697,M697=K697,M6 97=L697),"OK","NO")

=IF(M693=H693,"OK",IF(M693=I693,"OK",IF(M693=J693 ,"OK",IF(M693=K693,"OK",IF(M693=L693,"OK","NO")))) )

Thank you very much for your help.

If you are not getting a result of OK, then none of your equalities are evaluating to TRUE. In other words, although you may believe that M697=I697 (or one of the others), it does not.
The value stored in the cell does not change as a result of formatting, unless you have checked the option Precision as Displayed (which is almost never recommended).
To test for numeric equality, especially with calculated values, you should either test for a very small difference (depending on the level of precision you feel is suitable), or explicitly ROUND the values to your desired level of precision.