combining IF OR Functions

[kevan]

I am trying to combine the following if functions with an or funtion which
will provide mw with the answer 6,10,14,21 or 25.
=IF($AG$2="3-6","6", "Not OK")
=IF($AG$3="7-10","10", "Not OK")
=IF($AG$4="11-14","14", "Not OK")
=IF($AG$5="18-21","21", "Not OK")
=IF($AG$6="22-25","25", "Not OK")

My attempts so far are futile. Can anyone help?

Thanks

 

[smartin]

I am trying to combine the following if functions with an or funtion which
will provide mw with the answer 6,10,14,21 or 25.
=IF($AG$2="3-6","6", "Not OK")
=IF($AG$3="7-10","10", "Not OK")
=IF($AG$4="11-14","14", "Not OK")
=IF($AG$5="18-21","21", "Not OK")
=IF($AG$6="22-25","25", "Not OK")

Hi kevan,

Be aware when you use double-ticks in a formula like =IF($AG$2="3-6" you
are asking if AG2 literally contains the text 3-6. If AG2 has a proper
number in it like 5 the IF statement will always be false.

You could try a formula like

=IF(AND($AG$2>=3,$AG$2<=6),6,"Not OK")

Note this formula also outputs the /number/ 6, not a text result of "6".

 

[ShaneDevenshire]

Hi,

There may be a logic error here, if AG2 does not contain 3-6 the formula
returns "Not OK". Once that is done the function would not go on to check
AG3. If it did it would return something like Not OK, Not OK, Not OK, 21,
Not OK. Would you really want all those answers. Here is probably what you
want:

=IF($AG$2="3-6",6,IF($AG$3="7-10",10,IF($AG$4="11-14",14,IF($AG$5="18-21",21,IF($AG$6="22-25",25, "Not OK")))))

You don't need to quote numbers so I took those quotes out.

 

[kevan]

Thanks Shane it worked perfectly once I had corrected my own error about cell
numbers....

=IF($AG2="3-6",6,IF($AG2="7-10",10,IF($AG2="11-14",14,IF($AG2="18-21",21,IF($AG2="22-25",25, "Not OK")))))

Thanks again

 

[Shane Devenshire]

Hi Kevan,

Well that makes a lot more sense to me,

Good luck.
Shane