Finding 1st,2nd,3rd etc

  • Thread starter Thread starter checkQ
  • Start date Start date
C

checkQ

NAME AMOUNT
MAX 20
CLIFF 5
CLIFF 38
MAX 40
CLIFF 60
DOT 50
CLIFF 60
CLIFF 60
CLIFF 78
CLIFF 60
DOT 44
I have a data table with names and scores. Sometimes the names are repeated.
Cliff is repeated 4 times. Does anyone knows a formula that will tell me that
Cliff's lowest score is 5 and his second to lowest score is 38? 60 would be
his 3rd lowest and 78 would be his 4th. I tried using small, but small
indicates that 60 is the 3rd , 4th and 5th lowest with 78 being the 6th .
In reality, 78 is not his 6th lowest but his 3rd.
 
=SMALL(IF(A2:A20="CLIFF",B2:B20),1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Replace ,1 with ,2 etc.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
If the Names are in A2:A12 and Amounts in B2:B12 then put your name [Cliff]
in D1 then this formula in D2 copied down will give you the successive lowest
values for that name with no repeats

=MIN(IF(A$2:A$12=D$1,IF(ISNA(MATCH(B$2:B$12,D$1:D1,0)),B$2:B$12)))

This is an array formula which needs to be confirmed with CTRL+SHIFT+ENTER

Note: once amounts are exhausted for that name you get zeroes.....
 
Oops. I retested the formula but 60 was stillshown to be the 3rd , 4th and
5th lowest with 78 being the 6th . Do you know anyway around this Bob?
 
Back
Top