A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Help with array formula



 
 
Thread Tools Display Modes
  #1  
Old January 20th, 2005, 03:34 PM
external usenet poster
 
Posts: n/a
Default 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  
Old January 20th, 2005, 04:55 PM
Jason Morin
external usenet poster
 
Posts: n/a
Default

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  
Old January 20th, 2005, 05:17 PM
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 10:59 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.