I have a worksheet with data in column a1:a10 and a ranking column in b1:b10.
I need to skip ranking a3, a6 and a9 but I need to leave the data in the
cells the way they are. What function can I use to do this? The Ranking
function doesn't seem to like skipping cells.

Try this in B1:

=IF(MOD(ROW(),3),RANK(A1,(A$1:A$2,A$4:A$5,A$7:A$8, A$10)),"")

Copy down to B10

Biff

"casdaq" wrote in message
...
I have a worksheet with data in column a1:a10 and a ranking column in
b1:b10.
I need to skip ranking a3, a6 and a9 but I need to leave the data in the
cells the way they are. What function can I use to do this? The Ranking
function doesn't seem to like skipping cells.

Thanks so much - That worked! How do you I use this same function where I
need to skip other rows? Say I had data in columns a2:a21 and I needed to
skip cells A4,A5,A7,A8,A15,A16?

"T. Valko" wrote:

Try this in B1:

=IF(MOD(ROW(),3),RANK(A1,(A$1:A$2,A$4:A$5,A$7:A$8, A$10)),"")

Copy down to B10

Biff

"casdaq" wrote in message
...
I have a worksheet with data in column a1:a10 and a ranking column in
b1:b10.
I need to skip ranking a3, a6 and a9 but I need to leave the data in the
cells the way they are. What function can I use to do this? The Ranking
function doesn't seem to like skipping cells.

Since there is no pattern use a helper column and identify the numbers to
exclude with something like "skip".

...........A..........B
1.......28............
2.......70............
3.......62............
4.......64......skip
5.......81......skip
6.......42...........
7.......58......skip
8.......19......skip

Then enter this formula in C1 and copy down to C8:

=IF(B1="",SUMPRODUCT(--(B$1:B$8=""),--(A1<A$1:A$8))+1,"")

Biff

"casdaq" wrote in message
...
Thanks so much - That worked! How do you I use this same function where I
need to skip other rows? Say I had data in columns a2:a21 and I needed to
skip cells A4,A5,A7,A8,A15,A16?

"T. Valko" wrote:

Try this in B1:

=IF(MOD(ROW(),3),RANK(A1,(A$1:A$2,A$4:A$5,A$7:A$8, A$10)),"")

Copy down to B10

Biff

"casdaq" wrote in message
...
I have a worksheet with data in column a1:a10 and a ranking column in
b1:b10.
I need to skip ranking a3, a6 and a9 but I need to leave the data in
the
cells the way they are. What function can I use to do this? The Ranking
function doesn't seem to like skipping cells.

Or.....

If you'll tell us why you need to exclude certain numbers and there is some
logic behind it we might be able to put that logic in a formula and do away
with the need for a helper column.

Biff

"T. Valko" wrote in message
...
Since there is no pattern use a helper column and identify the numbers to
exclude with something like "skip".

..........A..........B
1.......28............
2.......70............
3.......62............
4.......64......skip
5.......81......skip
6.......42...........
7.......58......skip
8.......19......skip

Then enter this formula in C1 and copy down to C8:

=IF(B1="",SUMPRODUCT(--(B$1:B$8=""),--(A1<A$1:A$8))+1,"")

Biff

"casdaq" wrote in message
...
Thanks so much - That worked! How do you I use this same function where I
need to skip other rows? Say I had data in columns a2:a21 and I needed to
skip cells A4,A5,A7,A8,A15,A16?

"T. Valko" wrote:

Try this in B1:

=IF(MOD(ROW(),3),RANK(A1,(A$1:A$2,A$4:A$5,A$7:A$8, A$10)),"")

Copy down to B10

Biff

"casdaq" wrote in message
...
I have a worksheet with data in column a1:a10 and a ranking column in
b1:b10.
I need to skip ranking a3, a6 and a9 but I need to leave the data in
the
cells the way they are. What function can I use to do this? The
Ranking
function doesn't seem to like skipping cells.