Complicated ranking off of multiple variables formula

[broman5000]

Hey there,

I'm trying to create a ranking and quintile off of multiple criteria (consider these columns a, b, c, d, e)

Essentially I want to do the following

1. Create a ranking *(Column D) based off of the Usage value (column C) by item (Column B) against how each location (Column A) perform for that particular item (Column B)

2. I then would likely create a quintile ranking (Column E) of numbers (1,2,3,4,5) based of of those ranking values

The formula for this is way over my head and i'm not sure it's even possible! Help Please

obviously there is much more data than this, Its normally around 200K rows, this is just a snipit example that I would hopefully be able to drag the formula's down for

Column A - Location
Column B - Item
Column C - Usage
Column D - Rank
Column E - Quintile Rank

location item Usage rank quintile
a 1 0.25
a 2 0.65
a 3 0.47
b 1 0.60
b 2 0.68
b 3 1.00
c 1 0.52
c 2 0.08
c 3 0.63
d 1 0.17
d 2 0.15
d 3 0.31
e 1 0.42
e 2 0.11
e 3 0.19
f 1 0.02
f 2 0.28
f 3 0.53

[Kaper]

How about starting with pivot table with locations in rows and items in columns headers (or may be in filter field) and volume in data filed?
Then you would have at least chance to prepare first step - ranking of how each location performed in each item.

By the way - attachment (workbook) with sample data and sample (manually assessed) output is almost always a good idea.

[broman5000]

Hey Kaper,

I tried that, but then I have a pivot table with 500+ columns and 70+ rows (file is too big to attach)
I've seen where someone has tried a sumproduct or countif formulas to do this, but I tried and I've been unsuccessful
I was hoping just to write a formulas in column D and E and drag down...Should I think about concatenating to get uniques?

[broman5000]

For example to show on the ranking

location item Usage rank quintile
a 1 0.25 3
a 2 0.65 5
a 3 0.47 3
b 1 0.60 6
b 2 0.68 6
b 3 1.00 6
c 1 0.52 5
c 2 0.08 1
c 3 0.63 5
d 1 0.17 2
d 2 0.15 3
d 3 0.31 2
e 1 0.42 4
e 2 0.11 2
e 3 0.19 1
f 1 0.02 1
f 2 0.28 4
f 3 0.53 4

[Kaper]

Even if not attachment (with small but representative subset of data with real delicate info replaced by dummy names) then

attachment (workbook) with sample data and sample (manually assessed) output is almost always a good idea.

is (at least for me in this case)_ needed to understand what you want to achieve.

[Kaper]

Or may be I misunderstood you.
Because besides column E (here my question about what is expected output) you were also asking in post #1 about formula for column D (rank)

It could be:

Please Login or Register  to view this content.

[broman5000]

So I tried that and It doesn't pull down correctly...I think it has to do with the fact that it isn't referencing anything in column A (The location)...Let me ask this differently (See below)

I would need to rank each item performance (Column B) based off of its usage (column C) relative to those same items (Column B) from other locations (Column A)

So for example below (Location 1, Item 3 has a usage of 20%, which puts it at a rank of 2 as it is the 2nd lowest of that certain item as it compares to how each store is using that item)

excel help.PNG
​​​​​​​Does that help?

[FlameRetired]

Pics and screenshot are seldom of any use. Post an actual Excel workbook ... saves retyping data.

If you are not familiar with how to do that:

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[broman5000]

I think i uploaded it correctly, do you see it?

[Glenn Kennedy]

ONE manually calculated result is not that helpful, a few would have been better...

But try:

=COUNTIFS($B$2:$B$31,B2,$C$2:$C$31,"<"&C2)+1

[broman5000]

This is awesome and it worked perfectly! Do you know how I would do the quintile formula based off of this results in the next column?

[Glenn Kennedy]

Yep. try this array formula:

=MATCH(C2,PERCENTILE(IF(B2=$B$2:$B$33,$C$2:$C$33),{0,0.2,0.4,0.6,0.8}),1)

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[broman5000]

This is great, thank you so much!