Ranking based on 3 criteria

[Jillybean9621]

Hi everyone. I've searched through the forums and haven't found exactly what I'm looking for just yet so I thought I'd ask.

How could I rank projects based on items, age, city (city is given a number) for example
A B C
5 73 Portland 13
2 107 Salem 33
2 153 tillimock 4

The lower the number on the city column they should have a higher importance. The higher age and higher item counts have the higher importance as well.

Does this make sense?
In this example tillimock woukd be #1 , Portland 2.

There would be a potential of 200 records that would need to be ranked.

 

[Jillybean9621]

You all are amazing! I'll try them tomorrow but m pretty sure they will work!

 

[BrianJN1]

In reference to the original ask, I understand that there are just 3 items of data to be processed, ie projects.

When I look at Marcelo's proposal, derived from Peter_SSs thinking, and then applying that to my consideration in comment #2 above, I see that Marcelo's formula creates a ranking at which one would arrive had one gone through the DATA tab process. The difference however is that the data is not reordered; one however must scan a list to ascertain an order.

[TABLE="width: 320"]
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>[TR]
[TD="width: 64"]Item[/TD]
[TD="width: 64"]Age[/TD]
[TD="width: 64"]City[/TD]
[TD="width: 64"]City Rank[/TD]
[TD="width: 64"] Rank
[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]73
[/TD]
[TD]Portland[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="align: right"]107
[/TD]
[TD]Salem
[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]153
[/TD]
[TD]Tillimock[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1
[/TD]
[/TR]
</tbody>[/TABLE]
Reproducing Marcelo's formula: =COUNTIF(D:D,"<"&D2)+COUNTIFS(D:D,D2,A:A,">"&A2)+COUNTIFS(D:D,D2,A:A,A2,B:B,">"&B2)+1

one should note that if those red symbols are reversed a very different ranking will occur.

 

[Marcelo Branco]

You wrote

The lower the number on the city column they should have a higher importance. The higher age and higher item counts have the higher importance as well.

So, i used < when comparing values in column D because of the red text above.
On the other hand, i used > when comparing values in columns A and B because of the blue text above.

M.

 

[BrianJN1]

With reference to Marcelo @ Comment #13 and mine directly above,
I was a little careless in proofreading, the text:

one should note that if those red symbols are reversed a very different ranking will occur.

should have read:
one should note that if any of those red symbols are reversed a very different ranking will occur.

 

[Peter_SSs]

In reference to the original ask, I understand that there are just 3 items of data to be processed,

Perhaps I have misunderstood what you mean, but from post no. 1 we have:

There would be a potential of 200 records that would need to be ranked.

 

[Marcelo Branco]

Sorry, Brian
I thought the OP (Jillybean9621) had made the comment in post 12
In fact, if we reverse all the comparison signals, i.e., the first < by > and the two > by < , the formula would remain consistent.
But the ranking would be reversed - the highest value would be the most important.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Item​

[/TD]
[TD]

Age​

[/TD]
[TD]

City​

[/TD]
[TD]

City Rank​

[/TD]
[TD]

Rank​

[/TD]
[TD]

Reversed Rank​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

5​

[/TD]
[TD]

73​

[/TD]
[TD]

Portland​

[/TD]
[TD]

13​

[/TD]
[TD]

4​

[/TD]
[TD]

3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

12​

[/TD]
[TD]

153​

[/TD]
[TD]

tillimock​

[/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

6​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

107​

[/TD]
[TD]

Salem​

[/TD]
[TD]

33​

[/TD]
[TD]

6​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

2​

[/TD]
[TD]

253​

[/TD]
[TD]

tillimock​

[/TD]
[TD]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

20​

[/TD]
[TD]

90​

[/TD]
[TD]

Portland​

[/TD]
[TD]

13​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

4​

[/TD]
[TD]

74​

[/TD]
[TD]

Portland​

[/TD]
[TD]

13​

[/TD]
[TD]

5​

[/TD]
[TD]

2​

[/TD]
[/TR]
</tbody>[/TABLE]

E2
Same formula (post #10 )

Formula in F2 copied down
=COUNTIF(D:D,">"&D2)+COUNTIFS(D:D,D2,A:A,"<"&A2)+COUNTIFS(D:D,D2,A:A,A2,B:B,"<"&B2)+1

M.

 

[Peter_SSs]

Here's an adaptation of my earlier suggestion to obtain the rankings without the need for a helper column.

Book1
	A	B	C	D	E
1	Item	Age	City	City Rank	Rank
2	5	73	Portland	13	4
3	12	153	tillimock	4	1
4	2	107	Salem	33	6
5	2	253	tillimock	4	2
6	20	90	Portland	13	3
7	4	74	Portland	13	5
Rank (2)

E2 copied down
=SUMPRODUCT(--((-$D$2:$D$7*10^6+A$2:A$7*1000+B$2:B$7)>(-D2*10^6+A2*1000+B2)))+1

 

[BrianJN1]

Perhaps I have misunderstood what you mean, but from post no. 1 we have:

Hmmm! I'm trying to requote Peter_SSs but the imbedded quotes do not seem to be working. Anyway that's an aside.

Peter_SSs, Jillybean9621's original data offered 3 records and so I went with that. True, more records could be ranked but given the sample of 3 I did not try to extrapolate. I also went with the assumption that each City was intended to be unique in the extended listing; we weren't told if Items or Age should be split or further addressed against a location.

I'd be interested to know if "Tillimock" for instance was to occur more than once would its value of importance differ understanding that the City Value is most important.

 

[Peter_SSs]

Hmmm! I'm trying to requote Peter_SSs but the imbedded quotes do not seem to be working.

Yes, quotes within quotes don't automatically appear, you have to re-insert them manually if you want them there.

.. we weren't told if Items or Age should be split or further addressed against a location.

Not explicitly, but I think that the the following given formula and the request to add a third condition indicates that Items and Age are still to be included in the ranking assessment.

So =a1*1000+b1 and then I rank that column. I'm now trying to figure out how to add the third column into the rank.

I'd be interested to know if "Tillimock" for instance was to occur more than once would its value of importance differ understanding that the City Value is most important.

If its value does differ on different rows, Marcelo's formula, and mine, would pick that up and rank accordingly. For example, Portland in this sample.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F
1	Item	Age	City	City Rank	Peter	Marcelo
2	5	73	Portland	13	4	4
3	12	153	tillimock	4	2	2
4	2	107	Salem	33	5	5
5	2	253	tillimock	4	3	3
6	20	90	Portland	2	1	1
7	4	74	Portland	44	6	6
Rank (2)

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(--((-$D$2:$D$7*10^6+A$2:A$7*1000+B$2:B$7)>(-D2*10^6+A2*1000+B2)))+1
F2	=COUNTIF(D:D,"<"&D2)+COUNTIFS(D:D,D2,A:A,">"&A2)+COUNTIFS(D:D,D2,A:A,A2,B:B,">"&B2)+1

 

[BrianJN1]

Peter_SSs,
All three of us are working upon conjecture. That formula "= a1*1000 + b1" is still something that I haven't comprehended.

Anyway, I'm sure that our poster will enlighten us as to how the scenario should be evaluated.
Thank you (and Marcelo) for your insights, they have been of some assistance to me.