Ranking Function

[Guest]

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.

 

[T. Valko]

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

 

[Guest]

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]

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

 

[T. Valko]

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