multiple if

[bernie777]

Hi guys im struggling again

how can you say the following:
if any of the C17,18, E16, E17G16,G17,I16,I17 ARE 0-3 THEN 0, 4-20 THEN 1, 20+ THEN 2


=+IF(OR(Summary!$C$17<=3,Summary!$C$18<=3,Summary!$E$16<=3,Summary!$E$17<=3,Summary!$G$16<=3,Summary!$G$17<=3,Summary!$I$16<=3,Summary!$I$17<=3),0,IF(OR(Summary!$C$17<=20,Summary!$C$18<=20,Summary!$E$16<=20,Summary!$E$17<=20,Summary!$G$16<=20,Summary!$G$17<=20,Summary!$I$16<=20,Summary!$I$17<=20),1,IF(OR(Summary!$C$17>=21,Summary!$C$18>=21,Summary!$E$16>=21,Summary!$E$17>=21,Summary!$G$16>=21,Summary!$G$17>=21,Summary!$I$16>=21,Summary!$G$17>=21),2,)))

How do you say the same with and?
thanks a lot

[AliGW]

Try using the LOOKUP or VLOOKUP function.

[sktneer]

You may try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. after placing the formula in the cell, hold down the Ctrl+Shift and then press Enter). When an array formula is entered correctly, you will notice in the formula bar that the formula is surrounded by the curly braces. And if you don't see the curly braces, press F2 when the cell containing the formula is selected and confirm it again with Ctrl+Shift+Enter.

Please Login or Register  to view this content.

Is this what you are trying to achieve?

[bernie777]

Hi i dont thin array is my solution, vlookup doest work in this case because i have to set logic and not just look up data
I need to say that if either of these fields (c17, c18, e17,e18, d17, d18 etc) are between 1-3 then give me a result as 0, if they are between 4-10 then give me a result of 1 etc

Thanks

[bernie777]

I will try this later today thank u

[sktneer]

Your formula returns what you expect it to return. Then what do you want actually. The shortest version of this formula will be the following Array Formula.

Please Login or Register  to view this content.

It returns 0 if any of your target cells contain a value which is less than or equal to 3, returns 1 if any of your target cells contain a value less than or equal to 20 and returns 2 if any of your target cells contain a value which is greater than 20.

This completely matches with your requirement. Is there something else you are trying to achieve?

[bernie777]

Hi thanks that is what i need

[sktneer]

You're welcome.
If that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.