Formula Help...I tried but failed!

[flyguykorea]

Hello everyone.

This is my first post here. I've attempted to find the answer in previous
posts but can. Any help would be really appreciated.

I have 5 colums of results:

AZ2
BA2
BB2
BC2
BD2

I have 6 possible grades:

For 100-92..."Level 6"
For 91 - 79..."Level 5"
For 78 - 70..."Level 4"
For 69 - 50..."Level 3"
For 49 - 34..."Level 2"
Below 34......."Level1"

I need the output to be a Level based on the lowest result from each column.

I thought I could use the IF & OR function for this, but as far as I know it
works for only 2 types of results. Any help on what I could use to expand
this to include all the levels mentioned above?
:
This works...but just for 2 possible results!
=IF(OR(AZ2<=69,BA2<=69,BB2<=69,BD2<=69),"Level 3","Level 4")

Thanks in advance,
Phil

 

[Biff]

Hi!

Create a table like this somewhere on your sheet:

0 1
34 2
50 3
70 4
79 5
92 6

Assume this table is in the range BF2:BG7.

Use this formula to get your result:

=VLOOKUP(MIN(AZ2:BD2),BF2:BG7,2,1)

If all the cells in the range AZ2:BD2 are empty the
formula will return 1. To prevent that:

=IF(AND(AZ2="",BA2="",BB2="",BC2="",BD2=""),"",VLOOKUP(MIN
(AZ2:BD2),BF2:BG7,2,1))

Biff

 

[Roger Govier]

Set up a table on your sheet as follows

0 Level 1
34 Level 2
50 Level 3
70 Level 4
79 Level 5
92 Level 6

Name this table RESULTS

then use the following formula
=VLOOKUP(MIN(AZ2:BD2),RESULTS,2)

 

[flyguykorea]

Thank you both very much for your help...all sorted out now!

Cheers