• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using index, small, rows in an array

C

cdickson

New Member
I'm having trouble using an array that will allow me to extract (the top three) several results (names) based on number of points scored. The only names I want are the top three. I've used the following array formula:
=IF(ROWS(A$97:A98)>4,"",INDEX($A$4:$A$52,SMALL(IF(T$4:T$52<=$T$54,ROW(T$4:T$52)-ROW(T$4)+1),ROWS(A$97:A98))))

Cell T54 contains the maximum score and so I want to return the individual with that score and the next two highest. Currently it returns the first three names beginning with cell A54.

My thought is that I can not use <= in the array. Is that correct? How can I resolve this?
Thanks,
 
Hi Dickson ,

Try this :

=IF(ROWS(A$97:A97)>4,"",INDEX($A$4:$A$52,MATCH(LARGE(IF(T$4:T$52<=$T$54,T$4:T$52),ROWS(A$97:A97)),T$4:T$52,0)))

The logic of your question does not need SMALL and ROW ; what we need to do is to extract the largest , the second largest and third largest scores , and then match these scores in the data range to retrieve the people names.

The segment in BLUE does the extraction of the largest , second largest and third largest values.

Secondly , ROWS(A$97:A98) will return 2 ; you need to start with ROWS(A$97:A97) which will return 1.

The formula will work only if there are no duplicates in the scores.

Narayan
 
Hi,

Perhaps this will also works:

=IF(ROWS(A$97:A97)>4,"",INDEX($A$4:$A$52,SMALL(IF($T$4:$T$52>=LARGE($T$4:$T$52,3),ROW($A$1:$A$8)-ROW($A$1)+1),ROWS(D$97:D97)))

Regards,
 
@Narayan,
Thanks, it worked great but ...
... there's the rub - it will only work if there are no duplicates. There are and so I need to extract each of the duplicates. This is why I needed to use the SMALL and ROW .
Any 'other' ideas.
Thanks,
 
Hi Try below formula:

=IF(ROWS(A$97:A97)>SUMPRODUCT(--($T$4:$T$11>=LARGE($T$4:$T$52,3))),"",INDEX($A$4:$A$52,SMALL(IF($T$4:$T$52>=LARGE($T$4:$T$52,3),ROW($A$1:$A$8)-ROW($A$1)+1),ROWS(D$97:D97))))

and copy down.
 
Thanks to all for your help. I am still struggling with my spreadsheet. I've put in the following array:
=IF(ROWS($AQ$5:AQ5)>3,"",INDEX($A$4:$A$50,LARGE(IF($T$4:$T$50>=$AU$4,ROW($T$4:$T$50)-ROW($T$4)+1),ROWS($AQ$5:AQ5))))

and it returns the values as if its reading the rows from the bottom up. I'd like to return the values 1st, 2nd, 3rd by the value rather than row number.

I've uploaded a sample of the file I'm working with. I'm trying to extract the 'high point' values, etc. and have them listed in the appropriate order (highest, second, third) in the far right cells.

Thanks for all your help so far.
 

Attachments

Narayan,
Yes. I wanted to place a correct/working formula there and then copy the array for the remaining categories.

Thanks,
Chet
 
Hi Dickson,

I am still not sure about your requirement: In your file uploaded in comment #6, you are showing high point in cell Y4 as 188, But you are fetching names of 3 persons only, but instead you have 4 persons. Two of them got 188. Can you clarify why you need only one and on what conditions?

Regards,
 
The cell Y4 (188) is the third highest score. I used that to pick the scores that are equal to or higher than, thus giving me the 1st, 2nd, and 3rd (188) scores. In the data set I provided there is currently a tie for 3rd but as the scores accumulate over the year the ties will be relatively rare. In this worksheet I'm interested in the top score, 2nd score, and third score ... and listed in the order of scores rather than listed by ascending row number.

Thanks, Chet
 
Wow, I'm almost (more so than not) embarrassed. I did not open the file that Narayan Sir had edited for me. I just now did. It looks just like I had planned. I'll be able to fill in the remaining categories with that guidance. The use of min function .....
Thanks to all of you, very much. What a fantastic resource.
Chet Dickson
 
You must log in or register to reply here.
Back
Top