You can use Rows(1:1) as a substitute for the number 1.
When you do a copy - during the paste it converts up (automatically) to 2,
3, 4 etc..

The offset() has 3 parameters,
#1, The Anchor cell
#2 the number of rows you wish to "move" up or down from the Anchor row.
Negitives -2 (move up) Positive 2 (moves down)
#3 the number of columns you wish to "move" to the left or right from the
Anchor column.
Negitives -2 (moves left) Positive 2 (moves right)

Hope that helps,,

Jim May


"emarzuq" wrote:

Wow, thank you very much that works out great.

When I was using the Large I was saying =LARGE($B$1:$B$4,1) and then
basically copying down and changing the 1 into 2 into 3 and so on.. could you
explain the ROW(1:1) I know what it does but I don't understand how it works.

Also, I had no idea Offset exists either. Could you please explain how that
formula works? if not then don't worry about it, the information you gave me
worked out great.

Thanks,

Eyad

"JMay" wrote:

Short example:

In Range A1:B4 enter:

Bob 80
Ted 75
Carol 85
Alice 60

In C1 enter:
=LARGE($B$1:$B$4,ROW(1:1)) ' then copy C1 down to C4

In D1 enter:

=OFFSET($A$1,MATCH(C1,$B$1:$B$4,0)-1,0) ' then copy D1 down to D4.

If you insert or delete rows the Column C formula is going to HICK-UP as
the Row(2:2) will change to #REF! or the like -- The ROW(#:#) always need
to stay in the sequesnce (from beginning to end) as Row(1:1) sequentially to
Row(50:50), say.

Hope this helps,

Jim May




"emarzuq" wrote:

I have two columns The first has names of individuals the next has a list of
grades, is it possible for me to be able to make a formula in another cell
which will always list the students from the highest grade to the lowest
grade.

This is not a sort problem, because I want it to be able to do this
calculation when I change the grades around for example.

Thanks for your help in advance