ran into another problem......
Posted by Patrick on January 25, 2001 6:02 AM
In cells C40:C45 I have the names of people and the
cells next to the names D40:45 are scores and below the list
of mames ( in cell D46 )is the lowest score that occurred
excluding the 0.
How can I bring on the name of the person, just below the lowest score,
in cell D47, that achieved the lowest score?
List
Peter 74
James 94
Paul 104
Mary 35
Ted 67
Sally 0
Lowest 35
Name of lowest
I am using this
=MIN(IF(List<>0,List,FALSE))
to bring on the lowest score in cell D46
Thanks in advance
Posted by Aladin Akyurek on January 25, 2001 6:14 AM
Use
=INDEX(A2:A7,MATCH(SMALL(B2:B7,2),B2:B7,0))
where I assume the names to be in A2:A7 and scores in B2:B7.
This will give you the name of the first of all players that have the lowest non-zero score.
Aladin
Posted by Aladin Akyurek on January 25, 2001 6:30 AM
: cells next to the names D40:45 are scores and below the list : of mames ( in cell D46 )is the lowest score that occurred : excluding the 0. : How can I bring on the name of the person, just below the lowest score, : in cell D47, that achieved the lowest score? : Peter 74 : James 94 : Paul 104 : Mary 35 : Ted 67 : Sally 0 : Lowest 35 : Name of lowest : =MIN(IF(List<>0,List,FALSE)) : to bring on the lowest score in cell D46
Patrick: The above formula gives you the name you're looking for. Next to it, you might want to enter =SMALL(B2:B7,2) to display the lowest non-zero score.
Aladin
Posted by patrick on January 25, 2001 10:14 PM
This does work great...but when all six of the
people do have a score....then it would
carry to the second lowest
example this
=INDEX(B40:B45,MATCH(SMALL(C40:C45,2),C40:C45,0))
would return Peter
Peter 74
James 94
Paul 104
Mary 110
Ted 115
Sally 0
and when all have a score
it would retun
James
however it's really Peter....that's the winner
Peter 74
James 94
Paul 104
Mary 110
Ted 115
Sally 125
thanks again.and sorry to bother you with it
Posted by Aladin Akyurek on January 26, 2001 4:17 AM
people do have a score....then it would carry to the second lowest example this =INDEX(B40:B45,MATCH(SMALL(C40:C45,2),C40:C45,0)) would return Peter Peter 74 James 94 Paul 104 Mary 110 Ted 115 Sally 0 it would retun James however it's really Peter....that's the winner James 94 Paul 104 Mary 110 Ted 115 Sally 125
OK, I've been a bit sloppy first time: the formulas that I gave should have been more generic.
I take it that the names are in B40:B45 and the values in C40:C45.
Enter the following formulas:
B48 =IF(ROW()-ROW($B$48)<=$C$48,OFFSET(INDEX($B$40:$B$45,MATCH($C$48,$C$40:$C$45,0),1),ROW()-ROW($B$48),),"") [ copy down n rows; this n is to be found in C48 ]
C48 =IF(COUNTIF(C40:C45,">0")>=1,SMALL(C40:C45,COUNTIF(C40:C45,0)+1),"")[ If you wish,you may array-enter instead the following formula here: =IF(COUNTIF(C40:C45,">0")>=1,MIN(IF(C40:C45>0,C40:C45,"")),"")]
D48 =IF(ISNUMBER(C48),COUNTIF(C40:C45,C48),"")
The above set does what you want.
You can also use the following instead of the formula in C48 to get the name(s) of the winner(s), the ones with the lowest scores:
D40 =IF($D$48>=1,If(C40=$C$48,B40,""))[ Copy down to D41:D45 ]
PS. If you would like to have a file containing all these, drop me an email.
Aladin
