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]

Here is an actual based on data pulled this morning.

Co Age City Rk FOCUS
3 134.16 10 39
1 132.48 10 44
1 132.30 10 45
4 129.60 10 38
5 128.34 10 37
1 672.30 4 5
1 437.57 4 9
7 349.42 4 1
1 314.20 4 10
2 309.34 4 3
1 304.27 4 11
1 232.16 4 15
1 13.51 26 148
1 13.33 26 151
1 13.51 26 147
3 13.23 26 144
1 10.59 26 154


The focus was created using the formula from peter_SS or Marcelo =COUNTIF(D:D,"<"&D2)+COUNTIFS(D:D,D2,A:A,">"&A2)+COUNTIFS(D:D,D2,A:A,A2,B:B,">"&B2)+1


this formula =SUMPRODUCT(--((-$D$2:$D$7*10^6+A$2:A$7*1000+B$2:B$7)>(-D2*10^6+A2*1000+B2)))+1 gives me different results some pretty large differences in some cases.
Ct Age City Rank Focus
3 134.16 10 4
1 132.48 10 60
1 132.30 10 61
4 129.60 10 3
5 128.34 10 2
1 672.30 4 20
1 437.57 4 25
7 349.42 4 1
1 314.20 4 28
2 309.34 4 11
1 304.27 4 30
1 232.16 4 34
1 13.51 26 149
1 13.33 26 152
1 13.51 26 148
3 13.23 26 9
1 10.59 26 155
1 112.35 12 67
2 104.48 12 13


you all are extremely awesome

 

[Marcelo Branco]

The two formulas worked perfectly. All you have to do is to adjust the ranges accordingly your data (columns and rows).


[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]

Co​

[/td][td]

Age​

[/td][td]

City Rk​

[/td][td]

Marcelo​

[/td][td]

Peter​

[/td][/tr]

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

2​

[/td][td]

3​

[/td][td]

134,16​

[/td][td]

10​

[/td][td]

10​

[/td][td]

10​

[/td][/tr]

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

3​

[/td][td]

1​

[/td][td]

132,48​

[/td][td]

10​

[/td][td]

11​

[/td][td]

11​

[/td][/tr]

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

4​

[/td][td]

1​

[/td][td]

132,3​

[/td][td]

10​

[/td][td]

12​

[/td][td]

12​

[/td][/tr]

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

5​

[/td][td]

4​

[/td][td]

129,6​

[/td][td]

10​

[/td][td]

9​

[/td][td]

9​

[/td][/tr]

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

6​

[/td][td]

5​

[/td][td]

128,34​

[/td][td]

10​

[/td][td]

8​

[/td][td]

8​

[/td][/tr]

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

7​

[/td][td]

1​

[/td][td]

672,3​

[/td][td]

4​

[/td][td]

3​

[/td][td]

3​

[/td][/tr]

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

8​

[/td][td]

1​

[/td][td]

437,57​

[/td][td]

4​

[/td][td]

4​

[/td][td]

4​

[/td][/tr]

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

9​

[/td][td]

7​

[/td][td]

349,42​

[/td][td]

4​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]

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

10​

[/td][td]

1​

[/td][td]

314,2​

[/td][td]

4​

[/td][td]

5​

[/td][td]

5​

[/td][/tr]

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

11​

[/td][td]

2​

[/td][td]

309,34​

[/td][td]

4​

[/td][td]

2​

[/td][td]

2​

[/td][/tr]

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

12​

[/td][td]

1​

[/td][td]

304,27​

[/td][td]

4​

[/td][td]

6​

[/td][td]

6​

[/td][/tr]

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

13​

[/td][td]

1​

[/td][td]

232,16​

[/td][td]

4​

[/td][td]

7​

[/td][td]

7​

[/td][/tr]

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

14​

[/td][td]

1​

[/td][td]

13,51​

[/td][td]

26​

[/td][td]

14​

[/td][td]

14​

[/td][/tr]

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

15​

[/td][td]

1​

[/td][td]

13,33​

[/td][td]

26​

[/td][td]

16​

[/td][td]

16​

[/td][/tr]

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

16​

[/td][td]

1​

[/td][td]

13,51​

[/td][td]

26​

[/td][td]

14​

[/td][td]

14​

[/td][/tr]

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

17​

[/td][td]

3​

[/td][td]

13,23​

[/td][td]

26​

[/td][td]

13​

[/td][td]

13​

[/td][/tr]

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

18​

[/td][td]

1​

[/td][td]

10,59​

[/td][td]

26​

[/td][td]

17​

[/td][td]

17​

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

D2 copied down
=COUNTIF(C:C,"<"&C2)+COUNTIFS(C:C,C2,A:A,">"&A2)+COUNTIFS(C:C,C2,A:A,A2,B:B,">"&B2)+1

E2 copied down
=SUMPRODUCT(--((-$C2:$C$18*10^6+A$2:A$18*1000+B$2:B$18)>(-C2*10^6+A2*1000+B2)))+1

M.

 

[Peter_SSs]

D2 copied down
=COUNTIF(C:C,"<"&C2)+COUNTIFS(C:C,C2,A:A,">"&A2)+COUNTIFS(C:C,C2,A:A,A2,B:B,">"&B2)+1

E2 copied down
=SUMPRODUCT(--((-$C2:$C$18*10^6+A$2:A$18*1000+B$2:B$18)>(-C2*10^6+A2*1000+B2)))+1

Some further comments:

- I agree with Marcelo that both our formulas produce the same result on the new sample data if the ranges are adjusted correctly, but the adjustment to my formula should actually be
=SUMPRODUCT(--((-$C$2:$C$18*10^6+A$2:A$18*1000+B$2:B$18)>(-C2*10^6+A2*1000+B2)))+1

- Both our formulas will produce incorrect (& different) results if there are any rows among the data that are empty. If that is a possibility post back with details.

- If you choose to use Marcelo's formula structure, I would still restrict the ranges within the columns or the sheet could become quite slow if you actually have quite a few rows of data. For example, I copied the data and formulas down to row 2000 (and adjusted the ranges in my formulas). The column of my formulas took less than one second to calculate whereas Marcelo's column took more than 20 seconds. If his formula was range restricted then it calculated very quickly too. For example
=COUNTIF(C$2:C$2000,"<"&C2)+COUNTIFS(C$2:C$2000,C2,A$2:A$2000,">"&A2)+COUNTIFS(C$2:C$2000,C2,A$2:A$2000,A2,B$2:B$2000,">"&B2)+1

 

[Marcelo Branco]

I have noticed some controversy about how functions with IF, COUNTIFs, SUMIFs, AVERAGEIFs etc handle references to entire columns. I've read messages from some experts saying that such functions are smart enough to only evaluate rows that contain data. On the other hand, others claim that such references impair performance.

The test that Peter performed and shared with us reinforces this second position.

Thank you, Peter, for bringing some light into this discussion.

M.

 

[Peter_SSs]

Hi Marcelo

I think some of those claims of 'smart' functions are correct, but I can never remember which functions or what usage circumstance invokes that 'smartness'.
So I mostly take the conservative approach and try to restrict the rows anyway - I can't see a disadvantage in doing that.