View original (French)

Formula for balancing carpool expenses among friends

Solved
amidoons Posted messages 12 Status Membre -  
amidoons Posted messages 12 Status Membre -
Hello,

I created an Excel spreadsheet to track carpooling expenses.
I put a lot of thought into it, and I think we can simplify things, but the result is good.
The table takes into account whether we are a driver or a passenger on 2 different segments or if we are absent.
The results are displayed in a two-way table:



I can't find a formula to balance the accounts (a bit like on Tricount) to ensure that those who spend more than they should end up with nothing to pay (reading the sentence slowly helps to understand).

And I don't want any software like Tricount; the trips are different depending on the people and the weeks, there are absentees, and the table I made will be simpler to fill out than a pre-made software.

Thank you in advance for any help.

A.

2 réponses

Réponse 1 / 2
Raymond PENTIER Posted messages 58548 Registration date   Status Contributeur Last intervention   17 474
 
Hi.

I am pleased to propose the following formula, after quite a bit of trial and error, to be entered in cells B20 to F24:
=INDEX($A$12:$F$16;ROW()-19;)-
INDEX($A$12:$F$16;COLUMN()-1;ROW()-18)
and if you name the range A12:F16 as "Pl", this formula will be
=INDEX(Pl;ROW()-19;)-INDEX(Pl;COLUMN()-1;ROW()-18)
and for verification we will total the rows 20 to 24 and the columns B to F (between 20 and 24).
https://www.cjoint.com/doc/16_03/FCpoOTQIrIg_c-Users-PENTIER-Documents-CCM-tableau-Covoiturage-amidoons-.xlsx
The final table reads as follows: 
B20=0 ======> Caroline owes nothing to Caroline
C20=5.25 ===> Caroline owes €5.25 to Clémence
B21=-5.25 ==> Clémence receives €5.25 from Caroline

Best regards.
--
Retirement is great! Especially in the Caribbean... :-)
Raymond (INSA, AFPA, CF/R)
1
amidoons Posted messages 12 Status Membre 1
 
Hello Raymond,

Thank you for your response and for the time spent on the table (and thank you for the addition in the legend!).
It's much better than what I had done (even though I didn't understand the formulas used...).
But ideally, I would like to end up with a result like this:
- Caroline owes €16 to Marie
- Caroline owes €0.50 to Clémence
- Pauline owes €12.13 to Clémence
- Cora owes €2.13 to Clémence

I don't know if you see what I mean.
This way, for example, Cora doesn't give €3.40 to Clémence and then €2.50 to Marie but receives €1.90 from Caroline and Pauline on the other side.

I don't know if you or someone else can help me? The best thing is to start from the table proposed by Raymond, which is better made than mine.

Thanks again and enjoy your retirement in the Antilles ;-)

Amid
0
Raymond PENTIER Posted messages 58548 Registration date   Status Contributeur Last intervention   17 474 > amidoons Posted messages 12 Status Membre
 
  • But that's exactly what I proposed to you: 

In C20 we see that Caroline owes €5.25 to Clémence
In D20 we see that Caroline owes €1.88 to Cora
In E20 we see that Caroline owes €9.38 to Marie
In C22 we see that Cora owes €3.38 to Clémence
In E22 we see that Cora owes €2.50 to Marie
In C23 we see that Marie owes €0.88 to Clémence
In C24 we see that Pauline owes €5.25 to Clémence
In D24 we see that Pauline owes €1.88 to Cora
In E24 we see that Pauline owes €5.00 to Marie
Line 25 shows that: 
Caroline distributes €16.50
Clémence owes nothing to anyone; she recovers €14.75
Cora spends €2.13
Marie recovers €16.00
Pauline distributes €12.13
0
amidoons Posted messages 12 Status Membre 1
 
Indeed, hasty response, it's true that from the last column I have the results I need.

Thanks again for your help!

Amid
0