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.

 

[BrianJN1]

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.

Are you asking for a sort regime? Like, Highlight columns A B C and D and then select the Sort Tab?
There you will find the columns to add according to your criteria, and whether ascending of descending in value.

 

[Jillybean9621]

Thank you for your reply. No big exactly.

I've been able to do this using a ranking formula for the first two criteria by creating a data column first. 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.

 

[BrianJN1]

On that basis Portland would score 5073, Salem 2107 and Tillimock 2153 which would make the order Salem, Tillimock and Portland.

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.

You state that the city value has higher importance but then the rest of that sentence apparently now is suggesting a conflict.

In what order should things be ranked? Which is first, second and then third?

 

[Peter_SSs]

Welcome to the MrExcel board!

Can you also confirm whether the City and its ranking number are together in the same cell (eg "Portland 13" Is that in a single cell or "Portland" in one cell and "13" in the next column, or something else altogether?)

If city & ranking number are in the same cell, are all the ranking numbers 1 or 2 digits, or could they be longer? If longer, how long?

 

[Jillybean9621]

Welcome to the MrExcel board!

Can you also confirm whether the City and its ranking number are together in the same cell (eg "Portland 13" Is that in a single cell or "Portland" in one cell and "13" in the next column, or something else altogether?)

If city & ranking number are in the same cell, are all the ranking numbers 1 or 2 digits, or could they be longer? If longer, how long?

They are in separate cells.

 

[Jillybean9621]

On that basis Portland would score 5073, Salem 2107 and Tillimock 2153 which would make the order Salem, Tillimock and Portland.


You state that the city value has higher importance but then the rest of that sentence apparently now is suggesting a conflict.

In what order should things be ranked? Which is first, second and then third?

Tillamook would be the most important so if should be ranked the highest. Then Portland then Salem. But somehow I need to incorporate #of items and number of days.

 

[Marcelo Branco]

I suppose that columns A and B should be used only as tiebreakers. If so, what is the priority?

M.

 

[Peter_SSs]

They are in separate cells.

I've been able to do this using a ranking formula for the first two criteria by creating a data column first. 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.

So, could you just extend your original idea like this?

Book1
	A	B	C	D	E	F
1	Item	Age	City	City Rank	Helper	Rank
2	5	73	Portland	13	87005073	4
3	12	153	tillimock	4	96012153	1
4	2	107	Salem	33	67002107	6
5	2	253	tillimock	4	96002253	2
6	20	90	Portland	13	87020090	3
7	4	74	Portland	13	87004074	5
Rank

Cell Formulas
Range	Formula
E2	=(100-D2)*10^6+A2*1000+B2
F2	=RANK(E2,E$2:E$7)

 

[Marcelo Branco]

Another possible solution - using data sample above provided by Peter_SSs


[Table="class: grid"][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][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Item​

[/td][td]

Age​

[/td][td]

City​

[/td][td]

City Rank​

[/td][td]

Rank​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

5​

[/td][td]

73​

[/td][td]

Portland​

[/td][td]

13​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

12​

[/td][td]

153​

[/td][td]

tillimock​

[/td][td]

4​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

2​

[/td][td]

107​

[/td][td]

Salem​

[/td][td]

33​

[/td][td]

6​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

2​

[/td][td]

253​

[/td][td]

tillimock​

[/td][td]

4​

[/td][td]

2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

20​

[/td][td]

90​

[/td][td]

Portland​

[/td][td]

13​

[/td][td]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

4​

[/td][td]

74​

[/td][td]

Portland​

[/td][td]

13​

[/td][td]

5​

[/td][/tr]
[/table]

Formula in E2 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.