Formula Help...I tried but failed!

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

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)

--
Regards
Roger Govier
"flyguykorea" <[email protected]> wrote in message
news:[email protected]...
> 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

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

>-----Original Message-----
>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
>.
>

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

Cheers

"Roger Govier" wrote:

> 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)
>
> --
> Regards
> Roger Govier
> "flyguykorea" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>