Split or Distribute Bill Between Friends With Formula

[asolopreneur]

James, Roy, Rich, Charles, Julie, Alice, Hector, Vinton, Kiera, and Corey are travelling. They are staying at a flat at 33 USD per day. They all have money but not equal and they are going to pay in sequence as mentioned in column. If someone don't have enough money the next person is going to contribute.

They want to schedule all future expenses so they all know when their turn is and how much they need to pay. I've included few samples in excel.

I hope I get answer from this great community.

 

[Jeffrey Mahoney]

This is my take on things:

Unknown

	A	B	C	D	E	F
James
Roy
Rich
Charles
Julie
Alice
Hector
Vinton
Kiera
Corey
James
Roy
Rich
Charles
Julie
Alice
Hector
Vinton
Kiera
Corey

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Week[/TD]
[TD="align: center"]Who is going to Pay[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]Contribution[/TD]
[TD="align: center"]Balance[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]42[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]-3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]-16[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]-19[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]-3[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]67[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]-12[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]-16[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]-18[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-33[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]33[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]44[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(OR(E2<>"",D2<>""),SUMIFS($E$2:$E2,$C$2:$C2,C2)-SUMIFS($D$2:$D2,$C$2:$C2,C2),0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]