Excel - Good way to paste in formulas? 1

[ScottyDu]

I have data in an Excel spreadsheet similar to the following:

X Y Z
1 43 -
1 12 -
1 56 -
2 21 -
2 23 -
2 19 -
3 70 -
3 69 -
3 88 -

In column Z, I need a formula that will rank each number in column Y base on what is in column X. Lowest number = 1, and highest number = 3. It's hard to explain, but here is what I need the reult to be:

X Y Z
1 43 2
1 12 1
1 56 3
2 21 2
2 23 3
2 19 1
3 70 2
3 69 1
3 88 3

So, of the rows where column X has a '1', the row where Y has a 12 is ranked 1, the row where Y has a 43 is ranked 2, and the row where Y has a 56 is ranked 3. Then, on to the next group (where column X has a '2'... and so on.

Currently, I have this formula in the first row, column Z:

=RANK(C1,$C$1:$C$3,0)

In the next row, the only thing that changes is the row number:

=RANK(C2,$C$1:$C$3,0)

Same with the next row:

=RANK(C3,$C$1:$C$3,0)

But in the next row (where column X changes to a '2', many things change in the formula:

=RANK(C4,$C$4:$C$6,0)

...and so on. I'm just thinking there has to be a better way to do this. Any thoughts? Thanks!

 

[SkipVought]

Hi,

Copy the first formula.

Select the range you want the formula in.

Edit > Paste

ALSO...

Select the cell containing the FIRST formula. Notice the little square in the lower RH corner of the selection outline. Double-Click that little square

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[capetownandre]

Assuming the X,Y,Z are columns A,b,c and the data starts in row 2 the folowing will work, can be draged down for all rows


=RANK(B2,INDIRECT(("B" & (TRIM(A2)*3) -1 & ":B" & (TRIM(A2)*3) +1 )),1)