Help building Group Expense Sheet with Equal Allocation

J

jayo13

undefinedundefined
I am trying to build a Spreadsheet that will allow me to distribut
costs equally to all members of the group for vacation purposes. Fo
instance, if there are 10 members, and one group member pays $100, an
another pays $50, and the rest of the group does not contribute. Tha
means that each member is required to pay $15, but b/c members 1 &
already paid, they are entitled to reimbursement. What I want to b
able to calculate is how much each person in the group owes the othe
people. Obviously person one is entitled to $85 and member 2 i
entitled to $35, but I want to show where these funds are coming from.

I apologize if this is not the right forum as this is my first post.
genuinely appreciate any help, and if further clarfication is neede
please advise.

Thank you.

Jerem
 
R

Roger Govier

Hi Jeremy

Set up headings in A1:C1 of Name, Paid, To Pay
In A2:A11 Enter names of members
In B2:B11 Enter amounts already paid

In B22 enter =SUM(B2:B21)
(this is to allow you to add further group members if required)
In C2 enter =IF(A2="","",$B$22/COUNTA($A$2:$A$21)-B2)
and copy down the range C3:C21

This will then show the amount of funds to be paid by each member (negative
if overpaid already). If you add more members, then it will allocate any
further payments / refunds.


Regards

Roger Govier
 
J

jayo13

Roger - thank you for the response. Although your formulas are mor
efficient than mine, we still end up at the same place.

Using your examples, I created five members. Jeremy, Brian, Steve, Jo
and Dave. Paying 50, 20, 30, 25 and 15 respectively. That results i
-22,8,-2,3, and 13. So that checks can be written to each person
want to be able to say Dave owes "X Name" this much and thus he ha
satisifed all his debt as well as the creditor involved. Does thi
make sense?

In the example above Jeremy is owed 22 dollars, but I want to creat
the most efficient way to get Jeremy paid with the minimal amount o
transactions. I don't know if this can be done, but I am hoping i
can.

Thank you in advance
 
R

Roger Govier

I guess somebody might come up with a solution for you, but I can't see an
easy resolution to this one.

Testing various amounts paid, it seems to me that in most cases, 4 cheques
have to be written. In your scenario, either Brian, Joe and Dave each pay
their deficits to Jeremy, and he pays the surplus 2 ($'s, £'s or whatever)
to Steve, or one of Brian, Joe or Dave split their payment as £2 to Steve
with the balance to Jeremy, whilst the other two pay their full amounts to
Jeremy.

Whichever way, 4 cheques are required.

Time for bed now, so no further thoughts for tonight.

Regards

Roger Govier
 
Top