Rank a Column + 1 cell from 2nd Col

[Bettingmad]

I have 6 columns:

A B C D E F
274 1 269 ? 279 ?
275 2 270 ? 280 ?
276 3 271 ? 281 ?
288 4 283 ? 293 ?

Column B represents a simple 'Rank' of Column A

In the first row of column 'D' I want the rank of the 269 in relatio
to the figures in column 'A' which equals 1. I also want the rank o
270, 271 & 272 in relation to their position on Column 'A' - whic
would equal 1, 1, 4

In Column 'F' I want the rank of those figures in 'E' relation to th
values of column 'A'. Which would equal 4, 4, 4, 5

Any help would be much appreciate

 

[CLR]

I woiuld start column A at zero instead of your normal first number, and
then do the RANK formulas in column B starting with the "0" entry as "1",
and then make column A and B into a VLOOKUP table, and then put
VLOOKUP,(true) formulas in columns D and F..............I think this will
give you what you're after..........

Vaya con Dios,
Chuck, CABGx3

 

[Bettingmad]

I don't see how VLookup helps.....

 

[Frank Kabel]

Hi
try the following formula in D1:
=INDEX($B$1:$B$4,IF(ISNA(MATCH(C1,$A$1:$A$4,1)),1,MATCH(C1,$A$1:$A$4,1)
),0)
copy down
use the same formula for F1. One note: This will work for all values
except your last one 293. For this value the formula will return 4 as
well. So unleass your testing values are smaller than your original
matrix this will work (workaround: add a 5th row in columns AB)

HTH
Frank

 

[CLR]

The VLOOKUP formulas in column D would look up the values in column C in the
AB column range. The VLOOKUP formulas in column F would look up the values
in column E......

If you would like to email me personally a copy of your file, I will be
happy to modify it and send back, showing you what I'm talking about. Do
not send the file to the newsgroup. It worked fine on my test sheet,
according to my understanding of what you want.

Vaya con Dios,
Chuck, CABGx3

 

[immanuel]

Try this:

In B1:
=RANK(A1,$A$1:$A$4,1)
Drag down to B4.

Then in D1:
=RANK(C1,($A$1:$A$4,C1),1)
Drag down to C4.

In F1:
=RANK(E1,($A$1:$A$4,E1),1)
Drag down to F4.

/i.