If Function with multi symbol ">" & value in formula

[namialus]

Hi, I'm using following formula for my checksheet.

=IF(AND(B9="",C9=""),"",IF(OR(AND(C9="",B9>=10,B9<=> 1000),AND(B9>=10,C9>=10,B9<=> 1000,C9<=> 1000)),"OK","NG"))

I need to make the function to work on "> 1000" input value, but I can't enter the value "> 1000"
because Excel will correct it to "1000". Is there any other way to solve it?

Thank you in advance

[gak67]

I think the problem is the <=> part of your formula. To have 'Not Equal To' use <>. If you just want 'Greater Than' then just > is enough. Try

=IF(AND(B9="",C9=""),"",IF(OR(AND(C9="",B9>=10,B9> 1000),AND(B9>=10,C9>=10,B9> 1000,C9> 1000)),"OK","NG"))

[namialus]

Dear gak67,

Thank you for your feedback & advise.

FYI, my operator only will input "> 1000" into the "B14" cell for
value more than 1000, and it's a standard input value (refer attached file).

Thanks again

[namialus]

The range is between 10 to > 1000

[gak67]

Try this in D8 and copied down:

=IF(AND(B8="",C8=""),"",IF(OR(AND(C8="",B8>=10,B8<>"> 1000"),AND(B8>=10,C8>=10,B8<>"> 1000",C8<>"> 1000")),"OK","NG"))

If any number over 1000 is entered as '> 1000' you don't need the <=1000 check as the number has to be <=1000, because if it was more it would be entered as '> 1000'. I have therefore replaced the <=1000 check with a <>"> 1000" check.

Please note the operator must enter it as '> 1000'. If it is entered some other way (such as '>1000' or '> 1000 ') it will not work correctly. I would suggest putting this in a custom data validation and adding the appropriate input message and/or error alert:

=OR(B8="> 1000",AND(B8>0,B8<=1000))

(I am assuming the MOhm's cannot be negative).

[gak67]

You can have the same result though without worrying about including '> 1000' in your formula, and with a slightly shorter formula, with this:

=IF(AND(B8="",C8=""),"",IF(AND(B8>=10,B8<=1000),IF(OR(C8="",AND(C8>=10,C8<=1000)),"OK","NG"),"NG"))

[namialus]

Dear gak67,

Thank you for your reply.

I tried your suggested formula

=IF(AND(B8="",C8=""),"",IF(OR(AND(C8="",B8>=10,B8<>"> 1000"),AND(B8>=10,C8>=10,B8<>"> 1000",C8<>"> 1000")),"OK","NG"))

but then it can be input with any text with "OK" return value.

So I try to change the <> to = sign

=IF(AND(B14="",C14=""),"",IF(OR(AND(C14="",B14>=10,B14="> 1000"),AND(B14>=10,C14>=10,B14="> 1000",C14="> 1000")),"OK","NG"))

but then value input (eg.: 10 and above) the return value become NG

I heard about ISTEXT function, is it can be combine with this IF function?

Thank you again

[Glenn Kennedy]

Hi there. What about this (using a couple of hidden helper columns).

[namialus]

Hi Glenn,

Thank you for your suggestion.
If possible, I don't want to use hidden formula, I just need the formula
in the RESULT column only.
If I need to hide the column, it will effect my other worksheet setting.

Anyhow, thank you again for your advise

[Glenn Kennedy]

OK then. try this

=IF(AND(B9="",C9=""),"",IF(AND(OR(B9="",B9>=10,B9="> 1000"),OR(C9="",C9>=10,C9="> 1000",C9="> 1000")),"OK","NG"))

If you like it, it may need a slight modification, in case someone enters >1000 instead of > 1000.

[Glenn Kennedy]

Don't forget that the hidden rows can be ANYWHERE. On the same sheet, on another sheet. they DON'T have to be next door to your Table.

[namialus]

Thanks Glenn,

I think I will use this formula as it is & without modification. It works for me
Thanks again for your advise

[Glenn Kennedy]

You're welcome... and thanks.