Max(s5:s69)...help

[ForSale]

i have a list of names in column A and numbers corresponding to thes
names in columns C:N. now, i need to have one cell that will displa
the name of the person with the most 100's. let me know if i am on th
right track with making a column of =countif(c5:n5,100) and then at th
bottom of that column a =max(s5:s69). this is displaying the row wit
the most 100's, but how do i display the name of that person?
thank

 

[Aladin Akyurek]

Yes, you are on the right track with CoutIf per name. You need to apply a
Rank formula to the counts so obtained. See

http://tinyurl.com/34w7d

for the formula system you need.

 

[ForSale]

sure enough, that was the perfect formula! now, this one is to where i
there is a tie it will only display the first entry. is there a way t
make it display all of the ties? i have 70 names, so i'm sure tha
there will be more than one with the most 100's.
thanks

 

[Aladin Akyurek]

The link I quoted describes a formula system that will fetch all the ties of
a max count. For example:

A 4
B 4
C 2
D 4

where A has a count of 4, that is, 4 times a score of 100 along with C and
D. The formula system will retrieve A, B, and D.

Here another link where I show the system using a display:

http://www.mrexcel.com/board2/viewtopic.php?t=87693

 

[ForSale]

Thanks for the help. i have followed the instructions given on both of
the suggested links and i am still coming up with only the first top
ranking entry. i really need to see all of the entries with he top
number. i may be doing something wrong somewhere, but i tried both
examples twice.
thanks.

 

[Aladin Akyurek]

You have names in A5:A69.
You have figures/numbers in C5:N69, each row of which is associated with a
correponding name in A5:A69.
You count 100's in C5:N68 for each name in S5:S69, using:

=COUNTIF(C5:N5,100)

You want to list names whose count in S5:S69 is equal to MAX(S5:S69).

Right?

 

[ForSale]

Correct

 

[Aladin Akyurek]

In T5 enter & copy down:

=RANK(S5,$S$5:$S$69)+COUNTIF(S5:$S$5,S5)-1

In U3:

=MAX(IF(INDEX(S5:S69,MATCH(V3,T5:T69,0))=S5:S69,T5:T69))-V3

which you need to confirm with control+shift+enter (pressed at the same
time) instead of just with enter.

In V3 enter: 1

In V5 enter & copy down:

=IF(ROW()-ROW($U$5)+1<=$V$3+$U$3,INDEX($A$5:$A$69,MATCH(ROW()-ROW($U$5)+1,$T
$5:$T$69,0)),"")

which gives you the desired list of names.

 

[ForSale]

Thanks, it works, kinda. there are two rows of numbers for each name,
so for name 1, is c5:n6, name 2 is in row 7 & 8.
the only thing that i changed on your formulas was on columns S, made
it c5:n6.
now, if name 1 has a tie with the most 100's, then it only shows the
odd names, if not it only shows the even names. i hope this makes
sense and hope you can help me resolve it.
thanks again.

 

[ForSale]

Can anyone explain why this is only returning the results of every othe
name and a possible solution

 

[Aladin Akyurek]

It shouldn't. Why don't you post a 5-row sample with say no more than 3
scores per name? The sample should be representative of your data.

 

[ForSale]

Aladin 100 100

Tom 100 100

Frank 100 100

this returns Aladinin v5 and Frank in v7.

if i change the scores to

Aladin 100

Tom 100 100

Frank 100 100

it changes the results to Tom in v5 and nothing else.

The first result should obviously be all three names and the secon
should display Tom and Frank.
let me know if i need to post a better example.
thank

 

[Aladin Akyurek]

Those scores would constitute no problem for the formula system I described,
unless there is some mismatch between your goal and the system. Send me an
email so that I can send you a copy of the workbook showing the formula
system.

 

[ForSale]

okay, i guess i need your e-mail address. in the example given, ther
were three names, the first part of the example had all three with th
same amount of 100's, but it only displayed two.
i'll be happy to e-mail you though. thanks for the help

 

[Aladin Akyurek]

(e-mail address removed)

 

[ForSale]

Aladin, i sent you another e-mail. i appreciate your help, but i a
still having difficulties.
thanks