Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
flyguykorea
 
Posts: n/a
Default 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
  #2   Report Post  
Biff
 
Posts: n/a
Default

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=""),"",VLO OKUP(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
.

  #3   Report Post  
Roger Govier
 
Posts: n/a
Default

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" wrote in 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



  #4   Report Post  
flyguykorea
 
Posts: n/a
Default

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" wrote in 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Another difficult failed formula Ken M. Excel Worksheet Functions 2 February 5th 05 07:22 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 04:37 PM
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 01:49 PM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 04:16 AM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 01:27 AM


All times are GMT +1. The time now is 12:38 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"