Rank by Group - Help
Renegade
here can help out.
Given the data below, I wouild like to use the rank function to rank
ICP% for REP_SSN , but grouped by Supervisor. I am open to any
ideas, through pivot tables, functions or whatever.
I have about 60 different sups to process the ranking for, and hand
calculating is getting tiresome :-)
SUP REP_SSN ICP% DESIRED RESULT[rank by ICP%]
==== ========= ==== =============
ABE 123546579 0.98 1
ABE 654897123 0.63 3
ABE 987979876 0.22 5
ABE 321321321 0.55 4
ABE 753537969 0.77 2
BOB 846178667 0.9 1
BOB 938819365 0.65 3
BOB 103146006 0.24 5
BOB 112410076 0.57 4
BOB 121674145 0.79 2
CAL 130938215 0.93 1
CAL 140202285 0.58 3
CAL 158730425 0.5 4
CAL 167994494 0.72 2
Thanks in advance for any direction you can offer!
You can either respond here, or mail to
......Don.McKay@wcom.com......
Bernie Deitrick
Don/Renegade,
How about a UDF? This would be used like
=rankif($A$3:$A$16,A3,$C$3:$C$16)
where A3:A16 is where the SUPs are, A3 is the key for the ranking, and
C3:C16 is where the items to be ranked are.
The only caveat is that the function (as written) requires that the data
all be on the same row, though that could easily be changed.
HTH,
Bernie
Function RANKIF(IDRange As Range, ID As Range, RankRange As Range) As
Integer
Dim DataArray() As Double
myRow = ID.Row
IDColumn = IDRange.Column
RankColumn = RankRange.Column
myID = ID.Value
myRank = Cells(myRow, RankColumn).Value
myCount = Application.WorksheetFunction.CountIf(IDRange, myID)
ReDim DataArray(myCount)
For Each myCell In IDRange
If myCell.Value = myID Then
DataArray(myIndex) = Cells(myCell.Row, RankColumn).Value
myIndex = myIndex + 1
End If
Next myCell
RANKIF = 0
For i = 0 To myCount - 1
If DataArray(i) > myRank Then RANKIF = RANKIF + 1
Next i
RANKIF = RANKIF + 1
End Function
Tom Ogilvy
In E2 put in this formula
=MAX(($A$2:$A$15=A2)*ROW($A$2:$A$15))
And enter it with Ctrl+Shift+Enter Rather than just enter
in F2 put in this formula
=MIN(IF($A$2:$A$15=A2,ROW($A$2:$A$15),""))
And enter it with ctrl+Shift+Enter Rather than just enter
These formulas determine the starting and ending rows for the supervisors
name
In cell D2 put in this formula
=RANK(C2,OFFSET($C$2,F2-2,0,E2-F2+1,1))
Enter it normally.
Now select D2:F2 and drag fill down to the end of the data.
You will need to adjust the ranges $A$2:$A$15 to the actual range of your
data
I believe you could combine all this into one formula, but I haven't tested
it.
Regards,
Tom Ogilvy
MVP Excel
"Renegade" <no.mail.please@thanks> wrote in message
news:39ac4836...@news.mcit.com...
Renegade
for an acending Group Rank.
Have to enter it with Shift+Ctrl+Enter... what's that about?
the function:
=SUM(($A$3:$A$20=A3)*($C$3:$C$20<C3))+1
Don
On Tue, 29 Aug 2000 23:53:55 GMT, no.mail.please@thanks (Renegade)
wrote:
Seokho Moon
let's suppose that
A B C D
sup rep_ssn icp% rank
1
2
put this formula in D2
=SUM(($A$2:$A$20=A2)*($C$2:$C$20<C2))+1
And enter it with ctrl+Shift+Enter Rather than just enter
"Renegade" <no.mail.please@thanks> wrote in message
news:39ac4836...@news.mcit.com...
Gary Barlow
Ctrl+Shift+Enter will create an array formula.
Chip Pearson has a very good explanation of array formulas on his
excellent web site:
http://www.cpearson.com/excel/array.htm
The formula could be altered to:
=SUMPRODUCT(($A$3:$A$20=A3)*($C$3:$C$20<C3))+1
which is not an array formula.
HTH
Gary
______________________
My views, not Auntie's
On Wed, 30 Aug 2000 18:37:46 GMT, don....@wcom.com (Renegade) wrote:
>I received this one on e-mail....works great after changing the < to >
>for an acending Group Rank.
>
>Have to enter it with Shift+Ctrl+Enter... what's that about?
>
>the function:
>=SUM(($A$3:$A$20=A3)*($C$3:$C$20<C3))+1
>
>Don
>
>On Tue, 29 Aug 2000 23:53:55 GMT, no.mail.please@thanks (Renegade)
>wrote:
>