2

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.

Community
  • 1
  • 1
wils484
  • 275
  • 1
  • 3
  • 14
  • 1
    Is the first column in sorted order? But your second column-name seems a little mis-leading..? I'm unsure how a 'Week Number' becomes a Rank? If they are, actually, week-numbers then the rank wouldn't be needed(?). – Andy G Oct 18 '13 at 20:26
  • I didn't know about WeekNum when I asked the question, that is my solution here. My use case is assigning week numbers (as in Week 1 of campaign, week 2, etc) to events. But to answer your question, the first column would not necessarily be in sorted order. – wils484 Oct 18 '13 at 21:40

3 Answers3

2

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..

Andy G
  • 19,232
  • 5
  • 47
  • 69
1

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.

Stewbob
  • 16,759
  • 9
  • 63
  • 107
  • This is a nice solution if values are sorted, I should have specified, but my values are not necessarily in sorted order. – wils484 Oct 19 '13 at 00:11
0

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.

wils484
  • 275
  • 1
  • 3
  • 14