Highest Value of some sort

[dondondon]

hey, ur website seems very helpful and i will continue to use it to assist my quest at becoming an excel expert. right now, my status is novice....

my question for now is...
i have a list of statistics. total wins. total loses. and a win/loss ration column all relating to a group of people over a certain amount of time. is there a way to make one cell give me the person with the highes amount of wins without looking at the list? i want to be able to tell who has most wins, and highest win/loss ration so i dont have to go over the long list of numbers. any advice, and or help is much appreciated

soon to be excel expert,
don

[Mudface]

You could use the MAX function over your range of wins/ losses etc for this. HTH.

[sen_edp]

Hello Dondondon

this might help

Microsoft Excel - HtmlMaker.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
E2		=
	A	B	C	D
1	NAME	WINS	LOOSES
2	MIKE	5	4
3	TONY	10	6
4	SUSAN	15	9
5	JACK	20	5
6	JOHN	25	4
7
8		MAX WINS	:alert('JOHN')>=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6,0))
9		MAX LOOSES	:alert('SUSAN')>=INDEX(A2:A6,MATCH(MAX(C2:C6),C2:C6,0))
Sheet1

You can see the value of cells only click each above hyperlinks

_________________
Best Regards,
Andreas
using Excel 2000 - Windows 2000 prof.

[ This Message was edited by: sen_edp on 2002-05-15 02:25 ]

[ This Message was edited by: SEN_EDP on 2002-05-15 02:47 ]

[buaile]

The problem with Max in this instance is that it only gives you the actual highest number, not the corresponding persons name (which I think is what is required). A mixture of Index, match and max will be better.

Suppose there are four people in cells A1:A4 and their values are in cells B1:B4

=INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0))
will tell you who hasthe highest value in the list.

[Mudface]

Oops, you're right of course Buaile, didn't read the question properly (bit esrly for me ). Sen_Edp and yourself are on the right but Sen's formulas don't seem to return the correct people. Try the following modifications, based on Sen's data: -

Most wins: -

=INDEX(A2:A6,MATCH(MAX(B2:B6),B2:B6,0))

Most losses: -

=INDEX(A2:A6,MATCH(MAX(C2:C6),C2:C6,0))

Nice graphic btw .

[ This Message was edited by: Mudface on 2002-05-15 02:33 ]

[Aladin Akyurek]

On 2002-05-14 23:30, dondondon wrote:
hey, ur website seems very helpful and i will continue to use it to assist my quest at becoming an excel expert. right now, my status is novice....

my question for now is...
i have a list of statistics. total wins. total loses. and a win/loss ration column all relating to a group of people over a certain amount of time. is there a way to make one cell give me the person with the highes amount of wins without looking at the list? i want to be able to tell who has most wins, and highest win/loss ration so i dont have to go over the long list of numbers. any advice, and or help is much appreciated

soon to be excel expert,
don

What do you mean by: without looking at the list?

And, suppose that the participants, say, Mudface, sen_edp, buaile, all have the highest amount of wins, who would you like that "one cell" to return as 'winner'?

Would it be not better that you provide a sample of your data? If inclined to do so: Select an empty cell, type =, select 10 rows of the relevant data including labels, hit F9, Edit|Copy what you see, and paste it in the follow up post.

[Mudface]

On 2002-05-15 02:48, Aladin Akyurek wrote:

And, suppose that the participants, say, Mudface, sen_edp, buaile, all have the highest amount of wins, who would you like that "one cell" to return as 'winner'?

Hehe, at the minute it's

sen_edp and buaile 1-Mudface 0

No need for a formula.

.

[sen_edp]

Hello everyone

Mudface all credit for the nice xlstohtml code goes to COLO.
Aladin points out the problem with equal wins or losses,very well, and i think he can give us a clue

Thanks

[sen_edp]

Hello everyone

Mudface all credit for the nice xlstohtml code goes to COLO.
Aladin points out the problem with equal wins or losses,very well, and i think he can give us a clue

Thanks

[sen_edp]

On 2002-05-15 02:48, Aladin Akyurek wrote:
And, suppose that the participants, say, Mudface, sen_edp, buaile, all have the highest amount of wins, who would you like that "one cell" to return as 'winner'?

if anyone wants to find Aladin's solution
take a look at this:
http://www.mrexcel.com/board/viewtop...c=3146&forum=2