Statistics Majors: Ranking zero ratios

[JDGiants]

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>

 

[jorismoerings]

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
	A	B	C	D	E	F	G
1		IN	OUT	TrueRatio	TR_Rank	AdjustedRatio	AR_Rank
2	Alpha1	0	863	0	14	0,001159	17
3	Alpha2	0	51	0	14	0,019608	15
4	Alpha3	0	128	0	14	0,007813	16
5	Alpha4	5	500	0,01	13	0,01	13
6	Bravo1	78	259	0,301158	7	0,301158	7
7	Bravo2	15	383	0,039164	11	0,039164	11
8	Bravo3	8	202	0,039604	10	0,039604	10
9	Charlie1	65	270	0,240741	8	0,240741	8
10	Charlie2	15	383	0,039164	11	0,039164	11
11	Charlie3	139	708	0,196328	9	0,196328	9
12	Delta1	160	364	0,43956	5	0,43956	5
13	Delta2	139	424	0,32783	6	0,32783	6
14	Delta3	392	557	0,70377	3	0,70377	3
15	Echo1	1620	555	2,918919	2	2,918919	2
16	Echo2	3441	220	15,64091	1	15,64091	1
17	Echo3	392	557	0,70377	3	0,70377	3
Sheet1

Cell Formulas
Range	Formula
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

 

[tusharm]

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]

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]

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!