Ranking function with no ties

Hi All

I hope someone can help I have a column with lots of data in it (I have attached a small portion in the workbook) I need to rank evry six entries going down the column. I have googled and found the + countif option to break the ties but I cant seem to get it to work?

I hope someone can point me in the right direction.

Thanks

You can use this formula in B1:

=RANK(D1,INDEX(D:D,INT((ROWS($1:1)-1)/6)*6+1):INDEX(D:D,(INT((ROWS($1:1)-1)/6)+1)*6),1)

then copy down. This does not take account of ties - is that likely to happen in a group of six numbers?

Hope this helps.

Pete

Hi Pete

Thanks for the input, there is however many ties in my data that I need to address

Okay, you can use this formula in cell B1:

=RANK(D1,INDEX(D:D,INT((ROWS($1:1)-1)/6)*6+1):INDEX(D:D,(INT((ROWS($1:1)-1)/6)+1)*6),1) + COUNTIF(INDEX(D:D,INT((ROWS($1:1)-1)/6)*6+1):D1,D1)-1

then copy down.

Hope this helps.

Pete