Re: Display the max, then the next down, then the next down, etc.

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 *** 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 work***).
>
> 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 *** 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 *** 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 *** 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 *** 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.
>
>
>
.