Help building Group Expense Sheet with Equal Allocation

undefinedundefined
I am trying to build a Spreadsheet that will allow me to distribute costs equally to all members of the group for vacation purposes. For instance, if there are 10 members, and one group member pays $100, and another pays $50, and the rest of the group does not contribute. That means that each member is required to pay $15, but b/c members 1 & 2 already paid, they are entitled to reimbursement. What I want to be able to calculate is how much each person in the group owes the other people. Obviously person one is entitled to $85 and member 2 is 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. I genuinely appreciate any help, and if further clarfication is needed please advise.

Thank you.

Jeremy

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


jayo13 wrote:
> undefinedundefined
> I am trying to build a Spreadsheet that will allow me to distribute
> costs equally to all members of the group for vacation purposes. For
> instance, if there are 10 members, and one group member pays $100, and
> another pays $50, and the rest of the group does not contribute. That
> means that each member is required to pay $15, but b/c members 1 & 2
> already paid, they are entitled to reimbursement. What I want to be
> able to calculate is how much each person in the group owes the other
> people. Obviously person one is entitled to $85 and member 2 is
> 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. I
> genuinely appreciate any help, and if further clarfication is needed
> please advise.
>
> Thank you.
>
> Jeremy
>
>

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

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

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

Thank you in advance.

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


jayo13 wrote:
> Roger - thank you for the response. Although your formulas are more
> efficient than mine, we still end up at the same place.
>
> Using your examples, I created five members. Jeremy, Brian, Steve, Joe
> and Dave. Paying 50, 20, 30, 25 and 15 respectively. That results in
> -22,8,-2,3, and 13. So that checks can be written to each person I
> want to be able to say Dave owes "X Name" this much and thus he has
> satisifed all his debt as well as the creditor involved. Does this
> make sense?
>
> In the example above Jeremy is owed 22 dollars, but I want to create
> the most efficient way to get Jeremy paid with the minimal amount of
> transactions. I don't know if this can be done, but I am hoping it
> can.
>
> Thank you in advance.
>
>