Cool. Thanks for yet another good tip. For what we are creating now, it
should work just fine the way you first gave it to me. However, this actually
may come in handy for future reference. Thanks!
--
Have a nice day!
~Paul
Express Scripts,
Charting the future of pharmacy
"RagDyer" wrote:
As Lewis mentioned at the end of his explanation, ties are returned in their
respective order as entered in the original list.
If for some reason, you might prefer to have them (ties) listed in the
reverse order of their position in the original list, simply change the
Large() function to Small().
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Paul (ESI)" wrote in message
...
Awesome! Thanks, I think I've got it now. You have been a big help.
--
Have a nice day!
~Paul
Express Scripts,
Charting the future of pharmacy
"Lewis Clark" wrote:
I just had to fix a couple of cell references. Sorry.
******************************************
I spent some time studying this myself, and I think I can explain it to
you.
Though I could not have come up with it on my own. :)
The SMALL function, once you copy it down, simply sorts the rankings
from
lowest to highest. See the help for the SMALL function. The ROW
function
call (A1 in the first cell) returns "1" (which is the row containing the
cell A1). This tells SMALL to find the 1st lowest value. In the next
row,
the ROW(A2) will give you the second lowest rank, and so on down the
line.
In this example, this column of ranks is the range C2:C6 on sheet 2
The ROW function is a fancy way to get the numbers 1 thru 5 as you copy
the
formula down.
The IF/ROW functions will return an array to feed into LARGE. As array
functions they will cycle through the whole range (C2:C6) looking for
the
value in C2. For those rows that match C2, it will return the line
number
in the table (not the row number on the worksheet).
With this data:
Student Test Grade Rank
Meep 100% 1
Batman 90% 4
Superman 95% 2
Willy Wonka 95% 2
Veruca Salt 43% 5
If you put the whole INDEX formula in cell A2 on sheet 2, the IF/ROW
call
will return this array (internally):
1
false
false
false
false
Since "false" evalutes to zero, the "1" (which means the first row of
data)
is the largest value in this array. The COUNTIF will count only one
occurance of rank = 1. The LARGE function will find the largest value
in
the array - which is 1 - and pass this to the INDEX function, which will
pick off the name from the first row of data. When you copy this
formula to
cell B2 on sheet 2, it will do the same work and pick off the
corresponding
score.
So far, so good?
When you copy the INDEX formula to cell A3 on sheet 2, you are now
looking
for people and scores for rank = 2. The IF/ROW functions return the
following array:
false
false
3
4
false
and COUNTIF reports there are 2 occurrances of rank =2.
LARGE will pick off the 2nd largest (from COUNTIF) and return the 3 to
the
INDEX function, picking off the 3rd row of data.
When you copy to cell A4 on sheet 2, you still want rank = 2 (since
there is
a tie for 2nd place). IF/ROW returns:
false
false
3
4
false
BUT - the range for COUNTIF changes and ignores the rows above the
current
row, so COUNTIF returns 1. LARGE picks off the 1st largest value, and
passes 4 to the INDEX function, picking off row 4 of data.
A lot of this is done internally due to the way Excel handles array
functions, so you won't see most of this at work. When there are ties
in
rank, this will keep the tied people in the same relative order as they
were
in the original list.
Hope this helps.
|