IF function for OK and NC status based on numeric cell values

[fhadzafar]

i'm trying to get result in form of "OK" or "NC"for reporting purpose.
i.e.

IF(C3>=A3,"OK",(C3<=B3,"OK","NC"))

but formula doesn't work reading the numerical values from the cells(even not read as formula)
moreover, it should give the proper result whether both A3,B3 are filled or only one of them(i.e. B3 or C3)
All values in A3,B3,C3 are numeric.

regards,

Fahad

[samba_ravi]

Correct your formula as below
=IF(C3>=A3,"OK",IF(C3<=B3,"OK","NC"))
or try the below one
=IF(OR(C3>=A3,C3<=B3),"OK","NC")

[alansidman]

I'm not exactly sure what you are trying to calculate.

Tell us in simple language the criteria for OK. I cannot tell if you want C3>A3 and C3<B3 to be OK or if only one of the critieria is there then it is OK. Spell out the criteria for us.

[fhadzafar]

i want the outcome "OK" whenever (a)C3 is either equal to A3 or greater then A3 (b) is either less than B3 or equal to it.
it should also give OK when only one of the criteria is specified and the other one is missing.

And Siva, the suggested formulae are not working. I think it could work with a combination of ÏF & AND formulas.

Regards,

[fhadzafar]

Criteria are the values written in cells A3 and B3. I've also attached the file modified after adding OR function into the formula. but it doesn't work one value is not mentioned in cell B3.

[alansidman]

Based upon your explanation, Siva's formula

=IF(OR(C3>=A3,C3<=B3),"OK","NC") should be the correct answer. If this is not what you are looking for, then please provide examples of what you are attempting, with both OK examples and NC examples manually expressed.

[fhadzafar]

screen.PNG

Example provided in this screen shot please.

[alansidman]

I don't understand. In the second example, 31 is greater than 28. You indicated that your criteria is C3 is greater than A3 or C3 is less than B3. So this is true. Is your criteria now different?

[fhadzafar]

Yes you are right that it is true for Sr. # 2 where 31 is greater than 28. But, problem is that it is not the output of formula.
I've written it for demonstration purpose on screen only, as been asked.
Using the suggested formula the result is "NC" (which is wrong).

[martindwilson]

show us in another workbook

[fhadzafar]

needful done. hope it clarifies.

[martindwilson]

=IF(OR(C3>=A3,C3<=B3),"OK","NC")
why have you used * ?
=IF(OR(D3>=B3)*(D3<=C3),"OK","NC") also
since b3 is blank in scen.3 d3 is larger than b3 so you will get ok

[fhadzafar]

But what about the 2nd condition in the formula i.e. D3<=C3 which is not fulfilled but result is still ÖK" ? how can i cater this ?
using the formula =IF(OR(C3>=A3,C3<=B3),"OK","NC")

[martindwilson]

it says OR so either can be true try AND so both must be true to work
=IF(AND(D3>=B3,D3<=C3),"OK","NC")

[fhadzafar]

I've already tried AND. It doesn't give the required output. It gives NC for the case at Sr. #2
screen 2.PNG

[martindwilson]

try
=IF(D3="","",IF(OR(AND(B3="",D3<=C3),AND(C3="",D3>=B3)),"y",IF(AND(D3>=B3,D3<=C3),"y","n")))

[fhadzafar]

Great! Its working fine.
Thanks Martindwilson

[martindwilson]

thanks for the feedback,glad we could help