I share lifts with Sed and Awk to work every day. We tally journeys owed on a spreadsheet. A week might look like this:
Drive Cad Sed Awk
Day Sed Awk|Cad Awk|Cad Sed
Mon 2 2 | |
Tue | 2 2 |
Wed | | 1 2
Thu | 2 0 |
Fri | |
On Monday I drove both to work and back. On Tuesday Sed drove. On Wednesday I got a lift in with Awk, but took a train back. On Thursday Awk didn't come in to work. And on Friday we each drove ourselves.
Sometimes strange things happen. Sed and Awk might both drive separately, and I would get a lift in with one and return with the other. Still, the spreadsheet handles this just fine:
Drive Cad Sed Awk
Day Sed Awk|Cad Awk|Cad Sed
Mon | 1 0 | 1 0
Occasionally we will check that we haven't drifted too far from parity. At the top of the spreadsheet is a total (the letters below represent relationships.) If the numbers above the same letters are equal, we've reached parity:
Cad Sed Awk
S A | C A | C S
2 2 | 4 2 | 1 2
a b a c b c
Sed and Awk have reached parity. Unfortunately for me, a is 2:4 - I owe Sed 2 lifts. Time to drive!
A fringe scenario might occur where I owe Sed, who owes Awk, who owes me. Still, it's pretty easy to work that out by looking at the totals. (A radar graph would be nice!)
The problem is that there is a de-incentive to drive with less passengers. What is supposed to be a way to share petrol costs becomes a competitive taxi service. For example:
Drive Cad Sed Awk
Day Sed Awk|Cad Awk|Cad Sed
Mon 2 0 | |
Tue 0 2 | |
Wed | 2 2 |
Thu | | 2 2
In this scenario we have all lifted each other equally, and yet I have incurred twice the petrol costs. The way to get around this is to share the cost of the lifts.
Suppose I drive Sed and Awk to work. We split each journey between the 3 of us, so that each will owe me a 1/3. Driving back, I would earn another 1/3 from each. So 2/3 points per passenger per day for a full car. If the next day I drive, but only Sed is passenger, then I earn 1/2 for each journey. Today cost Sed more, because Awk wasn't on board to contribute. Bad for passenger, and bad for driver too. That's how it should be.
My question is: What formula can I use in the spreadsheet to calculate the fractions, and sum up the 6 columns into comparable totals? [While maintaining a moderately good looking spreadsheet]. I really want to avoid creating additional rows!
You might want to rethink the data you're keeping and consider the goal. If the goal is to maximize the ride-sharing, then you're crediting the wrong thing. I suggest that each time you drive, you get six points. Each person in the car with $n$ people in is awarded $-6/n$ points. So if you drive yourself, you get $6$ points, but also lose $6$. If you drive and bring Awk, you get $6$ but lose $3$, and awk loses $3$ as well. Roughly speaking, the idea is that driving one direction costs you, say, 6 rubles. That cost should be allocated among all passengers in the car. Hence the $-6/n$ rule. At the end of a week, folks with a positive tally are owed something by those with a negative tally.
This may not match your actual goals, but at least it's an accounting system designed with a particular cost-allocation in mind.