Trying to find the top three categories for Dashboard Metric

tchav

Hello,

I have a list in my primary column with categories ([Primary Column]52:[Primary Column]93). The column to the right COUNT column has counts for the categories ([count]52:[count]93). I also have a ranking column next to that with the following formula in it from row 52-93 —> =RANKEQ([Count]@row, Count52:Count93, 0).

What i am trying to do is get the top three categories to be listed.

If there are duplicates, say 10 micro holds and 10 finished product OOS sorbic, I want that to go to #1 and #2 (i dont care which order). I dont want it to skip duplicates. If there are 10 of each of those i want them to be top 1-3.

I am currently using these formulas

Top 3 Hold Metric Formulas:

 

=INDEX([Primary Column]52:[Primary Column]93, MATCH(MAX(Count52:Count93), Count52:Count93, 0))

 

=INDEX([Primary Column]52:[Primary Column]93, MATCH(LARGE(Count52:Count93, 2), Count52:Count93, 0))

 

=INDEX([Primary Column]52:[Primary Column]93, MATCH(LARGE(Count52:Count93, 3), Count52:Count$93, 0))

 

 

Top 3 Count Formulas:

 

=MAX(Count52:Count93, 1)

 

=LARGE(Count52:Count93, 2)

 

=LARGE(Count52:Count93, 3)

But they are giving me duplicates and i dont want the same category to be duplicated.

It should look like this:

Right now it is skipping the OOS Caffeine all together.

How can I make a formula to get this?

SoS | Dan Palenchar

Hello @tchav,

I would check out the solution below developed by @Preston Murphy with additions by @Jeff Reisman @Lindsay Whitbread @Devin Lee and @Kelly Moore

https://community.smartsheet.com/discussion/70947/ranking-without-duplicating-ranks