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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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.
>
>
>
.

Relevant Pages

  • Re: Formula logic explaination
    ... of criteria rather than a single cell. ... so the array contains a 1 as the first element. ... this is the way countif works traditionally. ... Apparently it was discovered that it can accept a contiguous range as the ...
    (microsoft.public.excel.programming)
  • Re: Count unique records?
    ... would return an array, for starters. ... If A2:A1000 contains 1 blank cell, ... What is the countif actually counting? ... When the first array is divided by the second, ...
    (microsoft.public.mac.office.excel)
  • Re: Count unique records?
    ... What is the countif actually counting? ... First, SUMPRODUCTis an array formula, so all the terms within it are ... FALSE if the cell is blank. ... When the first array is divided by the second, ...
    (microsoft.public.mac.office.excel)
  • Re: second highest digit value
    ... try this: Assuming the rank is in cell C1: ... This will give you the C1_th number in your data set. ... It is an array ...
    (microsoft.public.excel.misc)
  • Re: RANK
    ... Array enter the formula into a single cell, then copy the cell and select ... > I wish to rank five values in reverse order. ... >>MS Excel MVP ...
    (microsoft.public.excel.worksheet.functions)