Formula help! 3 logic tests, out come for each

[geoff0880]

Hey!

I'm coming a bit stuck on a formula that I'm trying to create. What I'm after is quite simple really

For example column A I'll have a number, anything between 1-30 and in column B I want to populate that with "Minor" "Medium" or "Major" depending on the number in column A

So 1 - 10 = Minor, 11 - 20 = Medium, 21 - 30 = Major

I don't suppose someone would know the formula for this would they. :D

 

[etaf]

you could probably use an array lookup
but heres an IF

=IF( AND ( A2 > =1, A2 < = 10) , "Minor" , IF ( AND ( A2 > =11, A2 < = 20), "Medium", IF ( AND ( A2 > =21, A2 < = 30), "Major" , "outside Criteria" )))

It could be simpler but was not sure if the number in A could be below 1 or above 30

 

[AlanY]

may be

=CHOOSE(ROUNDUP(A1/10,0),"Minor","Medium","Major")

 

[njimack]

A lookup table would be best as it would allow you to easily change the criteria, but here's a single formula that should work:
=LOOKUP(A2,{1,11,21,31},{"Minor","Medium","Major","Outside Criteria"})

 

[geoff0880]

Cheers guys, I'll give them all a go and see which one works for my data layout