hockeyguy11
New Member
- Joined
- Jun 18, 2019
- Messages
- 1
I need a formula that will help me rank the 'Jan Var. for rank only' values, ignoring blanks in between rows, in ascending order. So the largest negative will get rank 1, etc. Then if the values in that column are the same, I want to break the tie by looking at which row has the larger number of responses and award the higher rank to the higher number of responses, then if those values are still the same, award the higher rank to the lower number in the Jan (third) column, then if the values are still the same, award the higher rank to the lower number in the 'order of survey' column. I left in the 'Jan Rank' column which is using the RANK.EQ function but this function allows ties and so I am looking for help creating the COUNTIF/COUNTIFS tie breaker formula to add to it that will account for all the things I've mentioned. Thanks in advance!
<tbody>
</tbody>
Order of Survey | Exclusion category for ranking? | Jan | Jan Var. | Jan Rank | Jan Var. for rank only | Jan # Responses | Jan Weight | Feb | Feb Var. | Feb Rank | Feb Var. for rank only | Feb # Responses | Feb Weight |
1 | Yes | ||||||||||||
2 | 53.6 | -0.3 | 61 | -0.3 | 15501 | 98.5% | 53 | -0.6 | 38 | -0.6 | 16575 | 98.7% | |
3 | 61.5 | -1.4 | 54 | -1.4 | 15465 | 98.3% | 61 | -0.5 | 40 | -0.5 | 16540 | 98.5% | |
4 | 47.1 | -4.8 | 27 | -4.8 | 13402 | 85.2% | 46.9 | -0.2 | 44 | -0.2 | 14176 | 84.4% | |
5 | Yes | ||||||||||||
6 | 76 | -3 | 38 | -3 | 1310 | 8.3% | 76.2 | 0.2 | 48 | 0.2 | 1288 | 7.7% | |
7 | 69.4 | -4.3 | 32 | -4.3 | 1325 | 8.4% | 70.4 | 1 | 56 | 1 | 1300 | 7.7% | |
8 | 65.3 | -7 | 11 | -7 | 1305 | 8.3% | 70.8 | 5.5 | 65 | 5.5 | 1279 | 7.6% | |
9 | 61.9 | -7.7 | 9 | -7.7 | 1836 | 11.7% | 64 | 2.1 | 61 | 2.1 | 1849 | 11.0% | |
10 | 60.3 | -5.8 | 18 | -5.8 | 1831 | 11.6% | 60.6 | 0.3 | 50 | 0.3 | 1844 | 11.0% | |
11 | 53.4 | -9.5 | 6 | -9.5 | 1810 | 11.5% | 54.5 | 1.1 | 57 | 1.1 | 1814 | 10.8% | |
12 | Yes | ||||||||||||
13 | 67.1 | -0.8 | 59 | -0.8 | 15401 | 97.9% | 67.6 | 0.5 | 54 | 0.5 | 16495 | 98.2% | |
14 | 70.7 | -1.5 | 51 | -1.5 | 15559 | 98.9% | 70.9 | 0.2 | 49 | 0.2 | 16605 | 98.9% | |
15 | 72.1 | -3.5 | 34 | -3.5 | 4459 | 28.3% | 71.3 | -0.8 | 36 | -0.8 | 4590 | 27.3% | |
16 | 69 | -0.6 | 60 | -0.6 | 4381 | 27.8% | 68.3 | -0.7 | 37 | -0.7 | 4517 | 26.9% | |
17 | 77.1 | -2.8 | 40 | -2.8 | 4122 | 26.2% | 74.7 | -2.4 | 13 | -2.4 | 4256 | 25.3% | |
18 | 70.9 | -3.6 | 33 | -3.6 | 3657 | 23.2% | 69.2 | -1.7 | 23 | -1.7 | 3692 | 22.0% | |
19 | 70.9 | -6.4 | 14 | -6.4 | 4485 | 28.5% | 68.7 | -2.2 | 15 | -2.2 | 4610 | 27.5% | |
20 | 62 | -5 | 25 | -5 | 835 | 5.3% | 65.5 | 3.5 | 63 | 3.5 | 838 | 5.0% | |
21 | Yes | ||||||||||||
22 | 53.1 | -2.6 | 44 | -2.6 | 15321 | 97.4% | 52 | -1.1 | 31 | -1.1 | 16334 | 97.3% | |
23 | 66.3 | -3.2 | 36 | -3.2 | 15442 | 98.1% | 67.2 | 0.9 | 55 | 0.9 | 16479 | 98.1% | |
24 | 49.4 | -6.2 | 16 | -6.2 | 4488 | 28.5% | 47.2 | -2.2 | 17 | -2.2 | 4613 | 27.5% | |
25 | 66.9 | -3.4 | 35 | -3.4 | 4436 | 28.2% | 66.8 | -0.1 | 45 | -0.1 | 4560 | 27.2% | |
26 | 58.4 | -5.2 | 23 | -5.2 | 4387 | 27.9% | 55.7 | -2.7 | 11 | -2.7 | 4523 | 26.9% | |
27 | 65.5 | -4.6 | 28 | -4.6 | 4393 | 27.9% | 64.4 | -1.1 | 32 | -1.1 | 4520 | 26.9% | |
28 | 53.2 | -2.7 | 43 | -2.7 | 4363 | 27.7% | 51.9 | -1.3 | 28 | -1.3 | 4489 | 26.7% | |
29 | 56.3 | -4.4 | 31 | -4.4 | 4490 | 28.5% | 54.1 | -2.2 | 18 | -2.2 | 4622 | 27.5% | |
30 | 41 | -6.1 | 17 | -6.1 | 12860 | 81.7% | 40.6 | -0.4 | 41 | -0.4 | 13953 | 83.1% | |
31 | 42.4 | -5.5 | 20 | -5.5 | 12367 | 78.6% | 42.8 | 0.4 | 53 | 0.4 | 13226 | 78.8% | |
32 | Yes | ||||||||||||
33 | 47.3 | -7.9 | 8 | -7.9 | 1959 | 12.4% | 46.9 | -0.4 | 42 | -0.4 | 1933 | 11.5% | |
34 | 53.7 | -6.7 | 12 | -6.7 | 1963 | 12.5% | 53.6 | -0.1 | 46 | -0.1 | 1922 | 11.4% | |
35 | 69.8 | -5 | 25 | -5 | 1984 | 12.6% | 68.4 | -1.4 | 27 | -1.4 | 1978 | 11.8% | |
36 | 74.4 | -3.1 | 37 | -3.1 | 1886 | 12.0% | 74.4 | 0 | 47 | 0 | 1887 | 11.2% | |
37 | 53.8 | -4.5 | 29 | -4.5 | 1953 | 12.4% | 53.4 | -0.4 | 43 | -0.4 | 1933 | 11.5% | |
38 | Yes | ||||||||||||
39 | Yes | 57.5 | -8.8 | 724 | 4.6% | 58.5 | 1 | 751 | 4.5% | ||||
40 | Yes | 62.4 | -1 | 726 | 4.6% | 61.5 | -0.9 | 749 | 4.5% | ||||
41 | Yes | 76 | -0.5 | 725 | 4.6% | 71.4 | -4.6 | 751 | 4.5% | ||||
42 | Yes | 79.6 | -1.1 | 721 | 4.6% | 78.4 | -1.2 | 742 | 4.4% | ||||
43 | Yes | 61.8 | -4 | 723 | 4.6% | 61.3 | -0.5 | 747 | 4.4% | ||||
44 | Yes | ||||||||||||
45 | Yes | 53.9 | -6.7 | 1444 | 9.2% | 53.5 | -0.4 | 1396 | 8.3% | ||||
46 | Yes | 56.8 | -3.8 | 1448 | 9.2% | 53.7 | -3.1 | 1396 | 8.3% | ||||
47 | Yes | 69.8 | -3.2 | 1445 | 9.2% | 67 | -2.8 | 1396 | 8.3% | ||||
48 | Yes | 77.8 | -1.5 | 1440 | 9.1% | 74.7 | -3.1 | 1388 | 8.3% | ||||
49 | Yes | 55.8 | -3.9 | 1439 | 9.1% | 56.7 | 0.9 | 1383 | 8.2% | ||||
50 | Yes | ||||||||||||
51 | Yes | 52.7 | -7.1 | 347 | 2.2% | 37.6 | -15.1 | 340 | 2.0% | ||||
52 | Yes | 54.5 | -2.7 | 347 | 2.2% | 41.6 | -12.9 | 339 | 2.0% | ||||
53 | Yes | 68 | -3 | 347 | 2.2% | 64.3 | -3.7 | 339 | 2.0% | ||||
54 | Yes | 78.2 | -3.3 | 344 | 2.2% | 71.3 | -6.9 | 335 | 2.0% | ||||
55 | Yes | 53 | -6 | 345 | 2.2% | 41.9 | -11.1 | 339 | 2.0% | ||||
56 | Yes | ||||||||||||
57 | Yes | 60.2 | -3.6 | 161 | 1.0% | 44.8 | -15.4 | 154 | 0.9% | ||||
58 | Yes | 57.5 | -4.9 | 160 | 1.0% | 46.8 | -10.7 | 154 | 0.9% | ||||
59 | Yes | 70.2 | -8.2 | 161 | 1.0% | 64.3 | -5.9 | 154 | 0.9% | ||||
60 | Yes | 76.1 | -6.6 | 159 | 1.0% | 74.7 | -1.4 | 154 | 0.9% | ||||
61 | Yes | 56 | -1.7 | 159 | 1.0% | 41.6 | -14.4 | 154 | 0.9% | ||||
62 | Yes | ||||||||||||
63 | Yes | 47 | 4.1 | N/A | 0.0% | 44.9 | -2.1 | N/A | 0.0% | ||||
64 | Yes | 63.6 | 6.5 | N/A | 0.0% | 57.1 | -6.5 | N/A | 0.0% | ||||
65 | Yes | 76.9 | 12.2 | N/A | 0.0% | 66.2 | -10.7 | N/A | 0.0% | ||||
66 | Yes | 71.2 | 9.4 | N/A | 0.0% | 74 | 2.8 | N/A | 0.0% | ||||
67 | Yes | 42.4 | 8.1 | N/A | 0.0% | 37.7 | -4.7 | N/A | 0.0% | ||||
68 | Yes | ||||||||||||
69 | Yes | N/A | N/A | 0 | 0.0% | N/A | N/A | 0 | 0.0% | ||||
70 | Yes | N/A | N/A | 0 | 0.0% | N/A | N/A | 0 | 0.0% | ||||
71 | Yes | N/A | N/A | 0 | 0.0% | N/A | N/A | 0 | 0.0% | ||||
72 | Yes | N/A | N/A | 0 | 0.0% | N/A | N/A | 0 | 0.0% | ||||
73 | Yes | N/A | N/A | 0 | 0.0% | N/A | N/A | 0 | 0.0% | ||||
74 | Yes | ||||||||||||
75 | 64.8 | -3 | 38 | -3 | 918 | 5.8% | 59.7 | -5.1 | 4 | -5.1 | 885 | 5.3% | |
76 | 66.5 | -2.4 | 47 | -2.4 | 920 | 5.8% | 61.1 | -5.4 | 2 | -5.4 | 887 | 5.3% | |
77 | 73.5 | -1 | 58 | -1 | 920 | 5.8% | 69 | -4.5 | 5 | -4.5 | 889 | 5.3% | |
78 | Yes | ||||||||||||
79 | 49.6 | -9.6 | 5 | -9.6 | 956 | 6.1% | 46.4 | -3.2 | 9 | -3.2 | 933 | 5.6% | |
80 | 63.3 | -6.3 | 15 | -6.3 | 948 | 6.0% | 61.5 | -1.8 | 22 | -1.8 | 931 | 5.5% | |
81 | 77.2 | -1.3 | 56 | -1.3 | 925 | 5.9% | 75.5 | -1.7 | 24 | -1.7 | 901 | 5.4% | |
82 | 72.3 | -1.5 | 51 | -1.5 | 954 | 6.1% | 68.7 | -3.6 | 6 | -3.6 | 932 | 5.5% | |
83 | 49.4 | -6.4 | 13 | -6.4 | 952 | 6.0% | 46.3 | -3.1 | 10 | -3.1 | 922 | 5.5% | |
84 | 32.8 | -12.4 | 1 | -12.4 | 952 | 6.0% | 27.2 | -5.6 | 1 | -5.6 | 937 | 5.6% | |
85 | Yes | ||||||||||||
86 | 52.3 | -10.6 | 3 | -10.6 | 1000 | 6.4% | 48.9 | -3.4 | 7 | -3.4 | 1082 | 6.4% | |
87 | 63.8 | -8.6 | 7 | -8.6 | 867 | 5.5% | 65 | 1.2 | 58 | 1.2 | 926 | 5.5% | |
88 | 52 | -9.9 | 4 | -9.9 | 832 | 5.3% | 54.1 | 2.1 | 62 | 2.1 | 878 | 5.2% | |
89 | 61.9 | -7.7 | 9 | -7.7 | 818 | 5.2% | 63.8 | 1.9 | 60 | 1.9 | 865 | 5.2% | |
90 | 54.7 | -11.3 | 2 | -11.3 | 643 | 4.1% | 49.5 | -5.2 | 3 | -5.2 | 705 | 4.2% | |
91 | Yes | ||||||||||||
92 | 69.3 | -4.5 | 29 | -4.5 | 498 | 3.2% | 67.7 | -1.6 | 25 | -1.6 | 507 | 3.0% | |
93 | 76.5 | -5.4 | 21 | -5.4 | 497 | 3.2% | 80.1 | 3.6 | 64 | 3.6 | 507 | 3.0% | |
94 | 78 | -1.8 | 49 | -1.8 | 492 | 3.1% | 79.4 | 1.4 | 59 | 1.4 | 499 | 3.0% | |
95 | 75.4 | -1.4 | 55 | -1.4 | 480 | 3.0% | 73.1 | -2.3 | 14 | -2.3 | 480 | 2.9% | |
96 | 82.5 | -1.3 | 56 | -1.3 | 491 | 3.1% | 79.2 | -3.3 | 8 | -3.3 | 505 | 3.0% | |
97 | Yes | ||||||||||||
98 | 67.5 | -5.6 | 19 | -5.6 | 1765 | 11.2% | 65.9 | -1.6 | 26 | -1.6 | 1658 | 9.9% | |
99 | 81.2 | -2.5 | 45 | -2.5 | 1763 | 11.2% | 79.1 | -2.1 | 19 | -2.1 | 1657 | 9.9% | |
100 | 78.5 | -2.7 | 41 | -2.7 | 1758 | 11.2% | 76.5 | -2 | 20 | -2 | 1653 | 9.8% | |
101 | 68.5 | -1.5 | 51 | -1.5 | 1758 | 11.2% | 66.3 | -2.2 | 16 | -2.2 | 1649 | 9.8% | |
102 | 24.3 | -5.2 | 24 | -5.2 | 1709 | 10.9% | 23.4 | -0.9 | 35 | -0.9 | 1612 | 9.6% | |
103 | Yes | ||||||||||||
104 | Yes | 63.2 | -4.4 | 318 | 2.0% | 57.6 | -5.6 | 344 | 2.0% | ||||
105 | Yes | 75.2 | 0.3 | 315 | 2.0% | 70.6 | -4.6 | 344 | 2.0% | ||||
106 | Yes | 54.4 | -0.6 | 316 | 2.0% | 45.3 | -9.1 | 342 | 2.0% | ||||
107 | Yes | 28.7 | -2.7 | 296 | 1.9% | 21.1 | -7.6 | 332 | 2.0% | ||||
108 | Yes | 61.8 | -6.1 | 301 | 1.9% | 63.9 | 2.1 | 321 | 1.9% | ||||
109 | Yes | ||||||||||||
110 | Yes | 65.5 | -1.2 | 58 | 0.4% | 46.5 | -19 | 71 | 0.4% | ||||
111 | Yes | 75.9 | 0.9 | 58 | 0.4% | 71.4 | -4.5 | 70 | 0.4% | ||||
112 | Yes | 55.4 | -8.7 | 56 | 0.4% | 35.2 | -20.2 | 71 | 0.4% | ||||
113 | Yes | 82.6 | 27.6 | 23 | 0.1% | 75 | -7.6 | 40 | 0.2% | ||||
114 | Yes | ||||||||||||
115 | Yes | 71.4 | 71.4 | 35 | 0.2% | 53.7 | -17.7 | 54 | 0.3% | ||||
116 | Yes | 73.3 | 7.8 | 135 | 0.9% | 73.4 | 0.1 | 143 | 0.9% | ||||
117 | Yes | 67.2 | 14.6 | 134 | 0.9% | 59.3 | -7.9 | 145 | 0.9% | ||||
118 | Yes | 59.5 | 14 | 79 | 0.5% | 69.1 | 9.6 | 81 | 0.5% | ||||
119 | Yes | 63.9 | 97.2 | 36 | 0.2% | 30.2 | -33.7 | 53 | 0.3% | ||||
120 | Yes | ||||||||||||
121 | Yes | 100 | 100 | 2 | 0.0% | 83.3 | -16.7 | 6 | 0.0% | ||||
122 | Yes | 100 | 100 | 2 | 0.0% | 83.3 | -16.7 | 6 | 0.0% | ||||
123 | Yes | 100 | 100 | 2 | 0.0% | 50 | -50 | 6 | 0.0% | ||||
124 | Yes | 50 | 50 | 2 | 0.0% | -16.7 | -66.7 | 6 | 0.0% | ||||
125 | Yes | ||||||||||||
126 | Yes | 76.9 | 12.2 | 13 | 0.1% | 60 | -16.9 | 45 | 0.3% | ||||
127 | Yes | 76.9 | -5.5 | 13 | 0.1% | 82.2 | 5.3 | 45 | 0.3% | ||||
128 | Yes | 84.6 | 8.1 | 13 | 0.1% | 57.8 | -26.8 | 45 | 0.3% | ||||
129 | Yes | ||||||||||||
130 | Yes | 71.4 | -8.6 | 7 | 0.0% | 100 | 28.6 | 5 | 0.0% | ||||
131 | Yes | 71.4 | -28.6 | 7 | 0.0% | 100 | 28.6 | 5 | 0.0% | ||||
132 | Yes | 66.7 | -13.3 | 6 | 0.0% | -40 | -106.7 | 5 | 0.0% | ||||
133 | Yes | ||||||||||||
134 | 72.3 | 2 | 62 | 2 | 15281 | 97.1% | 71.2 | -1.1 | 33 | -1.1 | 16293 | 97.0% | |
135 | 79.4 | -1.9 | 48 | -1.9 | 4122 | 26.2% | 78.3 | -1.1 | 30 | -1.1 | 4256 | 25.3% | |
136 | 72.3 | 2 | 62 | 2 | 15281 | 97.1% | 71.2 | -1.1 | 34 | -1.1 | 16293 | 97.0% | |
137 | 74.7 | -2.7 | 41 | -2.7 | 3132 | 19.9% | 72.3 | -2.4 | 12 | -2.4 | 3157 | 18.8% | |
138 | 78.9 | -1.6 | 50 | -1.6 | 4180 | 26.6% | 79.3 | 0.4 | 52 | 0.4 | 4253 | 25.3% | |
139 | 75.5 | -2.5 | 45 | -2.5 | 3831 | 24.3% | 74.3 | -1.2 | 29 | -1.2 | 3959 | 23.6% | |
140 | 62.8 | -5.3 | 22 | -5.3 | 3806 | 24.2% | 63.1 | 0.3 | 51 | 0.3 | 3869 | 23.0% | |
141 | Yes | ||||||||||||
142 | 36.9 | 7 | 65 | 7 | 2863 | 18.2% | 36.3 | -0.6 | 39 | -0.6 | 2857 | 17.0% | |
143 | 37.5 | 4.2 | 64 | 4.2 | 2604 | 16.5% | 35.6 | -1.9 | 21 | -1.9 | 2593 | 15.4% |
<tbody>
</tbody>