Get rank of unique verion of an Excel range

Question

I am trying to get the ranks from a list of values, but I want the ranks to be based on a unique version of the list. What I'd like to do is something like

=RANK.EQ(value, UNIQUE(list_of_values))

I can't find out how to get the unique values within the formula. I've seen answers on how to output unique values to a new row, but my I don't require values to printed out, so I would hope there is an easier solution.

As an example, with a list like

|Week Ending|
|10/10/13|
|10/10/13|
|10/10/13|
|10/17/13|

the closest I get is RANK.EQ(value, list_of_values, 1), which gives me

|Week Ending|Week Number|
|10/10/13| 1|
|10/10/13| 1|
|10/10/13| 1|
|10/17/13| 4|

What I'm looking for is

|Week Ending|Week Number|
|10/10/13| 1|
|10/10/13| 1|
|10/10/13| 1|
|10/17/13| 2|

EDIT:

I didn't originally clarify this, but my values are not necessarily in sorted order.

Answer 1

For this specific example WEEKNUM() can be used to create an effective ranking of the dates:

=WEEKNUM(value, list_of_values, 1) + 1 - WEEKNUM(MIN(list_of_values))

This is because either all dates occur on the same day of the week, or dates occurring in the same week are considered to have the same rank.

"Week " can be concatenated to these values to create output of "Week 1", "Week 2" etc..

Answer 2

Via Andy G, =WEEKNUM() solves my problem. More generally,

= WEEKNUM(value, list_of_values, 1) + 1 - WEEKNUM(MIN(list_of_values))

Gives numbers that can be used to make "Week 1, Week 2", etc labels.

Answer 3

You can do it with fairly simple formulas, using two columns:

Assuming your dates are in A1:A10

B1 contains this formula: =RANK(A1,$A$1:$A$10)

Copy that formula down to B10

C1 contains this formula: =B1

C2 contains this formula: =IF(B2=B1,C1,C1+1)

Copy that formula down to C10

You can also use the RANK.EQ function instead of RANK.