If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with array formula
Hello friends,
I believe that the solution to my problem lies in the writing of an array formula; but since my creativity with writing array formula is very limited I am posing the question to you. Here is the scenario: I have a spreadsheet with details on the rankings of students in a class. We have rankings every few months (thus several times in a year). Thus, column A is the name of the student. Column B is the date when the ranking was taken. Column C is the rank of the student on that given date. Since several such rankings are done in a year; we have in column A duplicates. What I want to do is to find out the ranking of each student during the last such ranking exercise. On a separate TAB, in column A, I have already the names of the students (thus unique values from column A). But now my requirement is to have on this tab a column B that gives the ranking of the student during the chronologically last exercise done for ranking. Can you tell me what formula to use. As I mentioned above, I feel that it is an array formula; taking all the rankings for the matching student; and finally I need to take a MAX on the date of the ranking. But obviously I am unable to think out the algortihm on my own. (BTW: if array formula is not the solution then please feel free to recommend alternative strategies). Lots of thanks in advance for your help. |
#2
|
|||
|
|||
If your ranking data is on a sheet called "rank", try
this in B1 of the new sheet: =INDEX(rank!$C$1:$C$10,MATCH(A1&MAX(IF(rank! $A$1:$A$10=A1,rank!$B$1:$B$10)),rank!$A$1:$A$10&ra nk! $B$1:$B$10,0)) Array-entered (press ctrl/shift/enter). After array- entering, fill the formula down. HTH Jason Atlanta, GA -----Original Message----- Hello friends, I believe that the solution to my problem lies in the writing of an array formula; but since my creativity with writing array formula is very limited I am posing the question to you. Here is the scenario: I have a spreadsheet with details on the rankings of students in a class. We have rankings every few months (thus several times in a year). Thus, column A is the name of the student. Column B is the date when the ranking was taken. Column C is the rank of the student on that given date. Since several such rankings are done in a year; we have in column A duplicates. What I want to do is to find out the ranking of each student during the last such ranking exercise. On a separate TAB, in column A, I have already the names of the students (thus unique values from column A). But now my requirement is to have on this tab a column B that gives the ranking of the student during the chronologically last exercise done for ranking. Can you tell me what formula to use. As I mentioned above, I feel that it is an array formula; taking all the rankings for the matching student; and finally I need to take a MAX on the date of the ranking. But obviously I am unable to think out the algortihm on my own. (BTW: if array formula is not the solution then please feel free to recommend alternative strategies). Lots of thanks in advance for your help. . |
#3
|
|||
|
|||
Jason,
Great formula. Works like magic. Many thanks. -----Original Message----- If your ranking data is on a sheet called "rank", try this in B1 of the new sheet: =INDEX(rank!$C$1:$C$10,MATCH(A1&MAX(IF(rank! $A$1:$A$10=A1,rank!$B$1:$B$10)),rank!$A$1:$A$10&r ank! $B$1:$B$10,0)) Array-entered (press ctrl/shift/enter). After array- entering, fill the formula down. HTH Jason Atlanta, GA -----Original Message----- Hello friends, I believe that the solution to my problem lies in the writing of an array formula; but since my creativity with writing array formula is very limited I am posing the question to you. Here is the scenario: I have a spreadsheet with details on the rankings of students in a class. We have rankings every few months (thus several times in a year). Thus, column A is the name of the student. Column B is the date when the ranking was taken. Column C is the rank of the student on that given date. Since several such rankings are done in a year; we have in column A duplicates. What I want to do is to find out the ranking of each student during the last such ranking exercise. On a separate TAB, in column A, I have already the names of the students (thus unique values from column A). But now my requirement is to have on this tab a column B that gives the ranking of the student during the chronologically last exercise done for ranking. Can you tell me what formula to use. As I mentioned above, I feel that it is an array formula; taking all the rankings for the matching student; and finally I need to take a MAX on the date of the ranking. But obviously I am unable to think out the algortihm on my own. (BTW: if array formula is not the solution then please feel free to recommend alternative strategies). Lots of thanks in advance for your help. . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Array formula in merged cells | vtisix | Worksheet Functions | 8 | May 12th, 2015 06:08 PM |
Formula to Count and Return Most common Value in a Dynamic Named Range | Tinä | General Discussion | 1 | October 23rd, 2004 11:26 PM |
Can an array formula be used in a user defined function? | stratasmith | General Discussion | 3 | July 5th, 2004 07:45 PM |
OR function in an array formula | Frank Kabel | Worksheet Functions | 3 | May 13th, 2004 06:56 PM |