MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Refinement of MAX()


Posted by J Savery on September 06, 2001 11:17 AM

I am looking for a way to get a range of values to be qualified and generate the primary Max() value, then the secondary Max() value, and the tertieary Max() value without a sort for a bowling stat SS I have created.

This would be helpful as I need this for High Game/High Series by Male & Female & Team.

Thanks.

James S.


Posted by IML on September 06, 2001 11:25 AM

Use =large(range,n)

Posted by J Savery on September 07, 2001 8:08 AM

This works great!!

One more addition. Now that I can qualify with the =LARGE() function, can I also get this to give me the name affiliated with it using an =IF()statement which would be next to it in the SS.

e.g.

A B
Name1 100
Name2 200
Name3 175

=Large(b1:b3,1) = 200, with Name2 as bowler.

Maybe an array function??

Thanks,
J Savery

Posted by IML on September 10, 2001 7:46 AM

You can do this by formula
=INDEX(A1:B3,MATCH(LARGE(B1:B3,1),B1:B3,0),1)
In the case of multiple high scores, it will return the first bowlers name the list.

good luck