Formula for working out how much money each of 7 people owe each other.

Hi Everyone,

My friends and I went on this weekend trip, and we each covered different expenses depending on what people wanted to do - we didn't necessarily do everything together.

As such, we have this problem where people have paid for different things, covered each other - and now we all owe each other different sums of money.

I've set up a spread sheet with 4 tables:

- Car Hire
- Food
- Trips/Tours
- Miscellaneous

Each table has columns with titles:

- Item - what item was paid for
- Paid By - initials of the person who paid it
- Owing - this column has subheadings with each of our initials, who ever owes the person who paid money will have a '1' under their name.
- No. - this calculates the total number of people who used the particular item
- Total Cost - the total amount of money
- Cost Per Person - divides the total cost by the total number of people who used the item.

here's a link to the spread sheet if you want to see how it's set out:

https://docs.google.com/spreadsheets...it?usp=sharing

I really need help in working out how to calculate who owes who money. I was thinking of setting up something like the matrix shown below, with our initials in the first row and first column. The first row would be initials of who owes money, and first column will be who the money is owed to. Eg. in the example below it shows BC owes $20 to SH. I just need to work out what formula i can put in the cells in the matrix to make it calculate the right amount of money using the the tables in my spread sheet.


SH BC ZP DH SG1 SG2 CS
SH 20.
BC
ZP
DH
SG1
SG2
CS

Any ideas??? so stuck Not sure if i need vba?? Would really appreciate any help. Thanks!

Welcome to the forum!

Many members will not or cannot access file-sharing sites, so please attach your sample workbook here.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi,

Please upload the workbook here. Many of us prefer not to visit third party file hosting sites.

But can't you simplify this. Just add up the total of all the stuff that was bought for each activity and divide by the number of participants in that activity. Then take that total for each individual and deduct the amount each actually paid. If the result is positive they owe money, and if it's negative they are due that amount. Summarise the four activities, collect all the monies owed and distribute it to those who are due money.

@Richard - having looked at the Google Docs file, I would tend to agree - the process is being over-complicated.

Originally Posted by lirique

Hello Richard,

Thanks for your reply. Unfortunately it's not that simple as not everyone did everything and don't need to pay for everything - it'd be unfair to make people pay for an activity they did not do. If we just add all the costs together, and divide by 7, people will be paying for things they did not do.

That's not what Richard was suggesting: perhaps you need to read his post again.

The layout of your spreadsheet seems overly complicated: what you need first of all, in a very simple format, is a list of the activities, who paid for it, and the other participants. Once we have that, we can go from there. Something like this (KISS principle):

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I
1	Activity	Paid	Cost	Participant 1	Participant 2	Participant 3	Participant 4	Participant 5	Participant 6
2	Meal	Doc	£85.70	Dopey	Sneezy	Bashful
3	Theatre	Sleepy	£50.75	Doc	Sneezy	Grumpy	Bashful
4	etc.

Sheet: Sheet3

See attached Sheet 2.
Obviously double check the values I've entered.

I think you maybe had a typo with "The best jam in the universe of jams". You show this is for DH but then allocate it to SH. I've assumed it is in fact DH.