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

Ranking without Duplicating Ranks

Preston Murphy

Aug 28, 2020

I used the RANKEQ function for the first time recently and realized that any identical values will have the same rank. It will skip however many duplicates you had in the ranking numbers, so you may have a ranking scheme that looks like 1, 2, 3, 3, 3, 6, 7, 7, 9, 9.

This may be useful in some situations but it is actually problematic in other situations. Let's say I am searching for the top 10 values in my set. I will not find a value for 4, 5, 8, or 10. This will of course be frustrating if I want to simply plot the top 10 values from my data set.

I have created the following formula to ignore duplicates and simply increment the rank of any duplicates by 1. This means any duplicates will have an arbitrary ranking to each other.

In this formula, "A" is the column being ranked.

RANK =RANKEQ(A@row, A:A) + COUNTIF(A$1:A@row, RANKEQ(@cell, A:A) = RANKEQ(A@row, A:A)) - 1

Hopefully this helps someone!

---

View Post