Using a VLOOKUP & MAX/MIN formula

[t_w_haines]

Hi,

I am trying to create some grading charts for my teaching, the difficulty that I am having is that the marks are letters rather than numbers and therefore do not respond to standard formula.

"NYA" (not yet achieved)
"P" (pass)
"M" (merit)
"D" (distinction)


I would like to be able to type in the grades (as above) into a series/range of cells, then to set up a formula that finds the highest grade from that series/range.

e.g.

Typed data:

P
M
P
D


Formula to automate that the best grade here is D.


Any help would be most gratefully received,

Thanks

TH

 

[T. Valko]

Hi,

I am trying to create some grading charts for my teaching, the difficulty that I am having is that the marks are letters rather than numbers and therefore do not respond to standard formula.

"NYA" (not yet achieved)
"P" (pass)
"M" (merit)
"D" (distinction)


I would like to be able to type in the grades (as above) into a series/range of cells, then to set up a formula that finds the highest grade from that series/range.

e.g.

Typed data:

P
M
P
D


Formula to automate that the best grade here is D.


Any help would be most gratefully received,

Thanks

TH

Data in the range A2:A5.

Try this array formula**...

=INDEX({"NYA","P","M","D"},MAX(MATCH(A2:A5,{"NYA","P","M","D"},0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[t_w_haines]

Thanks for this,

I couldn't get this to work though (probably my inexperience with Excel!)

Here is a table to try and explain it a bit further

In the columns C3:C11 I have entered grades (NYA, P, M, D or blank)

In cell C12 I need the formula that takes the highest of those grades (where NYA is lowest, then P, then M, then D is highest, and blank is ignored)


This is then required for columns D, E, F & G as well.

A1	B	C	D	E	F	G
2	Assignments	GC1	GC2	GC3	GC4	GC5
3	Assig 1	P		NYA	NYA	M
4	Assig 2		M		P
5	Assig 3	NYA		D		D
6	Assig 4		D	M
7	Assig 5	M			P	M
8	Assig 6		P	D
9	Assig 7	NYA			M	M
10	Assig 8		D	P
11	Assig 9	M		P		NYA
12	OVERALL

<TBODY>
</TBODY>

Any further help would be wonderful!!

Thanks

 

[T. Valko]

Thanks for this,

I couldn't get this to work though (probably my inexperience with Excel!)

Here is a table to try and explain it a bit further

In the columns C3:C11 I have entered grades (NYA, P, M, D or blank)

In cell C12 I need the formula that takes the highest of those grades (where NYA is lowest, then P, then M, then D is highest, and blank is ignored)


This is then required for columns D, E, F & G as well.

A1	B	C	D	E	F	G
2	Assignments	GC1	GC2	GC3	GC4	GC5
3	Assig 1	P		NYA	NYA	M
4	Assig 2		M		P
5	Assig 3	NYA		D		D
6	Assig 4		D	M
7	Assig 5	M			P	M
8	Assig 6		P	D
9	Assig 7	NYA			M	M
10	Assig 8		D	P
11	Assig 9	M		P		NYA
12	OVERALL

<TBODY>
</TBODY>

Any further help would be wonderful!!

Thanks

Try this...

List the grades in a range of cells and list them in this specific order:

NYA
P
M
D

Let's assume they're in the range J2:J5.

Then, enter this array formula** in C12 and copy across as needed:

=INDEX($J2:$J5,MAX(IF(ISNUMBER(MATCH(C2:C11,$J2:$J5,0)),MATCH(C2:C11,$J2:$J5,0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.