Rank Duplicate Values With Duplicate Ranks Without Skipping Sequence

[bbaker]

Good afternoon.

I have an excel dashboard I'm creating and I need it to rank non-sequential columns and account for duplicates without skipping a number in the sequence. I want to rank the cells D25,G25,J25,M25,P25,S25,V25

As an example

CURRENT:

value Rank
3.15 7
4.93 3
4.73 4
4.73 4
5.13 1
5.13 1
4.73 4

WANT:

value Rank
3.15 4
4.93 2
4.73 3
4.73 3
5.13 1
5.13 1
4.73 3



This is how I would want it to rank. Currently it would rank 1,2,2,4,5,6.

The formula sequence I was trying to emulate based on this function I've found online for another spreadsheet:

=SUMPRODUCT((A2 < A$2:A$8)/COUNTIF(A$2:A$8,A$2:A$8))+1

My issue is that it is no sequential so I was trying to replace the A$2:A$8 with the comma separated list of cells to consider.

So based on the attachment when attempting to rank row 25 I have:

=SUMPRODUCT((D25 > (D$25,G$25,J$25,M$25,P$25,S$25,V$25))/COUNTIF((D$25,G$25,J$25,M$25,P$25,S$25,V$25),(D$25,G$25,J$25,M$25,P$25,S$25,V$25)))+1

I don't know if I'm completely off base or if there is an easier way to rank these and correctly account for the duplicates.

Thanks for your help.

 

[Chihiro]

Can't think of way to do this in single cell/range.

See attached for calculation with helper table.

Sheet2:
A2:A11 = Rank# as value in ascending order
B2:B11 = Using LARGE function to sort the range of values in descending order
C2:C11 = Extract unique values from B2:B11 (formula as CSE)

Sheet1:
Use Index,Match to find corresponding value in A2:A11.

 

[bbaker]

I think that solution would work to pull to another sheet, recalculate, then move info back. What makes that difficult for me is that a new tab is created each month for an updated dashboard. So with that same formula copied over into a new worksheet directing to the same sheet 2 i'm not sure what would happen.

 

[Chihiro]

See attached. You'd need to change Reference a bit, but would be quick change.

Better way might be to create calculation sheet, where you calculate monthly stats based on date picked in dashboard control and update stats on single sheet.

Something like below link.
http://chandoo.org/wp/2012/02/22/design-customer-service-dashboard/

 

[bbaker]

Chihiro,

Just wanted to thank you for helping me with this. I created a table the same as yours and built it on the same spreadsheet, just hidden away from the data so that it grabbed the data, sorted it, extracted unique, then posted rank based on those unique values. It worked perfectly and as they copy that tab for future dates it will carry over those formulas for the future tabs.

The link you posted looks very interesting and may be something I'll dig into for the future, but your fix was great for the time being.

Thanks!