# Statistics Majors: Ranking zero ratios

#### JDGiants

##### New Member
Probably a simple question. I am tracking a team's players donation ratio over various periods of time. Ratio is (IN) / (OUT). If I have multiple players with zero IN's, yet each with different amounts of OUT's, and I want to sort (rank) them by IN:OUT ratio, what is the best way to sort (rank) all of the players with a zero ratio? Am I off-base by simply adding 1 to all numerators of zero, and then calculating the ratio? Ultimate goal is to identify players with highest ratios. Will I lose accuracy in ranking when IN (or OUT) levels are extremely low, or conversely, extremely high? Simple example below (AdjustedRatio is numerator (IN) +1). TIA!

 IN OUT TrueRatio TR_Rank AdjustedRatio AR_Rank Alpha1 0 863 0.00000 14 0.0012 16 Alpha2 0 51 0.00000 14 0.0196 13 Alpha3 0 128 0.00000 14 0.0078 15 Alpha4 5 500 0.01000 13 0.0120 14 Bravo1 78 259 0.30116 7 0.3050 7 Bravo2 15 383 0.03916 11 0.0418 11 Bravo3 8 202 0.03960 10 0.0446 10 Charlie1 65 270 0.24074 8 0.2444 8 Charlie2 15 383 0.03916 11 0.0418 11 Charlie3 139 708 0.19633 9 0.1977 9 Delta1 160 364 0.43956 5 0.4423 5 Delta2 139 424 0.32783 6 0.3302 6 Delta3 392 557 0.70377 3 0.7056 3 Echo1 1,620 555 2.91892 2 2.9207 2 Echo2 3,441 220 15.64091 1 15.6455 1 Echo3 392 557 0.70377 3 0.7056 3

<tbody>
</tbody>

Last edited:

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### jorismoerings

##### Well-known Member
Hi,

Yes, you're off-base cause the ratio between 1 and 51, theoretically, will be the same as 71 and 3621 and hence will receive a higher number in your ranking.
I assume you don't want this to happen.
Solution could be to rank them on their possible ranking by using 1 as the numerator but always after you ranked all the true ratio's.
If that's a workable solution for you, try this:

Excel 2016 (Windows) 64 bit
ABCDEFG
2Alpha108630140,00115917
3Alpha20510140,01960815
4Alpha301280140,00781316
5Alpha455000,01130,0113
6Bravo1782590,30115870,3011587
7Bravo2153830,039164110,03916411
8Bravo382020,039604100,03960410
9Charlie1652700,24074180,2407418
10Charlie2153830,039164110,03916411
11Charlie31397080,19632890,1963289
12Delta11603640,4395650,439565
13Delta21394240,3278360,327836
14Delta33925570,7037730,703773
15Echo116205552,91891922,9189192
16Echo2344122015,64091115,640911
17Echo33925570,7037730,703773
Sheet1
Cell Formulas
RangeFormula
F2=IF(B2=0,1,B2)/C2
G2=1+SUMPRODUCT((\$B\$2:\$B\$17<>0)*(\$D\$2:\$D\$17>D2))+IF(B2=0,1+SUMPRODUCT((\$B\$2:\$B\$17=0)*(\$C\$2:\$C\$17)),0)
D2=B2/C2

Last edited:

#### tusharm

##### MrExcel MVP
Are you off base? Just compare alpha2 and alpha4.

The correct way to do this is to rank the non-zero IN players and *then* the zero IN players based on their OUT values.

I converted your data into an Excel table. Now, in a new column, add the formula =RANK.EQ([@TrueRatio],[TrueRatio])+SUMPRODUCT(([@IN]=0)*([IN]=0)*([OUT]<[@OUT]))

You'll get the expected results.
Probably a simple question. I am tracking a team's players donation ratio over various periods of time. Ratio is (IN) / (OUT). If I have multiple players with zero IN's, yet each with different amounts of OUT's, and I want to sort (rank) them by IN:OUT ratio, what is the best way to sort (rank) all of the players with a zero ratio? Am I off-base by simply adding 1 to all numerators of zero, and then calculating the ratio? Ultimate goal is to identify players with highest ratios. Will I lose accuracy in ranking when IN (or OUT) levels are extremely low, or conversely, extremely high? Simple example below (AdjustedRatio is numerator (IN) +1). TIA!

 IN OUT TrueRatio TR_Rank AdjustedRatio AR_Rank Alpha1 0 863 0.00000 14 0.0012 16 Alpha2 0 51 0.00000 14 0.0196 13 Alpha3 0 128 0.00000 14 0.0078 15 Alpha4 5 500 0.01000 13 0.0120 14 Bravo1 78 259 0.30116 7 0.3050 7 Bravo2 15 383 0.03916 11 0.0418 11 Bravo3 8 202 0.03960 10 0.0446 10 Charlie1 65 270 0.24074 8 0.2444 8 Charlie2 15 383 0.03916 11 0.0418 11 Charlie3 139 708 0.19633 9 0.1977 9 Delta1 160 364 0.43956 5 0.4423 5 Delta2 139 424 0.32783 6 0.3302 6 Delta3 392 557 0.70377 3 0.7056 3 Echo1 1,620 555 2.91892 2 2.9207 2 Echo2 3,441 220 15.64091 1 15.6455 1 Echo3 392 557 0.70377 3 0.7056 3

<tbody>
</tbody>

#### JDGiants

##### New Member
Are you off base? Just compare alpha2 and alpha4.

The correct way to do this is to rank the non-zero IN players and *then* the zero IN players based on their OUT values.

I converted your data into an Excel table. Now, in a new column, add the formula =RANK.EQ([@TrueRatio],[TrueRatio])+SUMPRODUCT(([@IN]=0)*([IN]=0)*([OUT]<[@OUT]))

You'll get the expected results.

Yep, works beautifully, thank you! Now, being me, I have to analyze the inner workings of your formula. I've recently started using tables in a small 'database', and this will definitely help my understanding, and use, of them. Much obliged - thanks!

#### tusharm

##### MrExcel MVP
You are welcome. And, yes, it helps learn about the product if you take the time to understand how things work.
Yep, works beautifully, thank you! Now, being me, I have to analyze the inner workings of your formula. I've recently started using tables in a small 'database', and this will definitely help my understanding, and use, of them. Much obliged - thanks!

### Forum statistics

1,191,485
Messages
5,986,861
Members
440,055
Latest member
CraigTriesHisBest

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back