Thread: Odd Ratio calculation - Give to Take

I have a list of people, each who give a certain amount of X. Occasionally they receive a certain number of Y for their troubles.

I'm looking for a formula that will let me sort a list of people based on the ratio of X to Y.

In other words, if:
Jim gave 10x and got 1y
Sara gave 10x and got 2y
Chip gave 1x and got 0y, and
Deb gave 3x and got 2y
I need to be able to sort them Jim, Sara, Chip, Deb.

Part of the trouble I'm encountering here is the division by 0 for Chip. Does anyone have any nifty suggestions for a formula that I can apply to a list like this, but of about 50 people?

Thank you, in advance!

You calculate the ratio of x to y by simply dividing teh x quantity by the y quantity. You can then sort the results in order from highest to lowest ratio. For example, Jim's ratio is 10/1 = 10, and Sara's is 10/2 = 5, so you would place Jim above Sara. As for Chip, his ratio is 1/0 = , so he goes above Jim. Can you figure out where Deb belongs on the list?

I had approached it that way originally, but the circumstance of the list should reflect Jim at the top, because he's given a lot of X, and hasn't received much Y in reward. Chip has only given 1 X, so shouldn't be above him on the list.

I suppose it's not really the basic ratio I'm after here, but how would one describe a formula that reflects what I noted above?

Here's a thought - from the data you can calculate an average value for the ratio of Y to X, which you can think of as the "expected" payback for giving X. For the 4 data points you provided, the average ratio of Y/X is 0.242, so for every X a person gives the average return is 0.242 Y. Then look to see how each person's actual return of Y differs from the expected return. What you find is this:

Calculations of actual - expected:
Jim: 1 - 0.242*10 = -1.42
Sara: 2 - 0.242*10 = -0.42
Chip: 0 - 0.242*1 = -0.242
Deb: 2- 0.242*3 = +1.274

If someone received the expected amount this formula gives 0. A negative number means you got less Y than average, and a positive number means you got more in return for your X. From this Jim got a lot less Y than he should have expected, and Deb got a lot more.

Does this help?

I think you've got it! That's exactly it - I need determine the average of the ratios, then sort by relation to the average for each person on the list.

Thank You!! I'll post back when I can confirm that I've got my head around this correctly ;}

I think the approach I suggested does exactly what yo're looking for. Next time there's some Y available, give it to Jim, and he will become even with Sara. After each "gift" of Y to an individual, you resort the list. You may have to define some tie breaker rules though.

And it's Deb who's the weasel, not Chip!

lol - Fix'd! - and indeed, Thank You