Calculate split bills considering variable occupation and multiple rooms

[johnnytominaga]

Hey there!

I'm working on a project where I need to calculate shared bills among multiple properties. The problem is that occupation varies across them, with people checking in and out, but bills needing to be calculated so that people can pay correct proportional amounts.

I've been trying to implement a solution where the user inputs:
a) Room/Property name (from a drop down list)
b) Date
c) Current bill amount
d) Amount already paid in the month

The database includes:
a) Booking ID
b) Room/Property name
c) Booking Start Date
d) Booking duration
e) Booking End Date
f) Person's Name

I've been trying to run something like:
a) Which rows include the room name inputted
b) Which rows include a start date lower than the date inputted and an end date higher than the date inputted
c) For each row that matches the criteria above, sum number of people at the room/property in each day of the month from the beginning of the month of the date inputted until either the end of the month or the end date of that record (sum(day 1/no. of people on day 1, day 2/no. of people on day 2, day 3/no. of people on day 3, ..., end date/no of people on end date) or sumproduct((range of dates in the month)*(range of no. of people per day)))
d) Output the Current bill amount inputted minus Amount already paid in the month divided by the sum result

So, the idea is to get a list of people staying at the property at Date inputted with the proportion amount each of them should pay based on the Current bill amount minus the Amount already paid in the month, for that specific room/property.

I unfortunately haven't managed to make this work.

Can anyone help me with this?

Thanks,


Johnny

 

[iggy_]

Can you share a sample of your database and the result you would like to see.

 

[johnnytominaga]

Can you share a sample of your database and the result you would like to see.

Hey!

Here we go:

Database:

https://ibb.co/fZieyc

Desired Result:
https://ibb.co/mJFsJc

Let me know if that helps.

Thanks a lot.

 

[iggy_]

Not the smallest formulas but here is what I came up with with an extra helper column C in the results tab.
Excel 2016 (Windows) 32 bit

	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​
2​	ID	Room		Start	Day	End	Status	Cost per month	Deposit	Agency Fee	Total Cost	Full Name	Nationality	Email	Phone
3​	1​	Apartment 1 -Room 1	Room 1	4/27/2018​	101​	8/6/2018​						Joe
4​	2​	Apartment 1	Room 2	5/5/2018​	11​	5/16/2018​						Byron
5​	3​	Apartment 1	Room 3	1/1/2018​	135​	5/16/2018​						Sung
6​	4​	Apartment 1	Room 4	2/22/2018​	128​	6/30/2018​						Elton
7​	5​	Apartment 2	Room 1
8​	6​	Apartment 3	Room 1
9​	7​	Apartment 4	Room 1
10​	8​	Apartment 4	Room 2
11​	9​	Apartment 4	Room 3
12​	10​	Apartment 1	Room 2	5/19/2018​	37​	6/25/2018​						Tillie
13​	11​	Apartment 1	Room 3	5/17/2018​	323​	4/5/2019​						Bryant

<tbody>
</tbody>

Sheet: Sheet6

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit

	B​	C​	D​
2​	Property	Apartment 1
3​	Date	5/18/2018​
4​	Total Bill Amount	200​
5​	Amount Paid	50​
6​
7​	Amount per Person	Days Owed	Amount Owed
8​	Joe	17​	$ 72.86​
9​	Byron	0​	$ -​
10​	Sung	0​	$ -​
11​	Elton	17​	$ 72.86​
12​	Tillie	0​	$ -​
13​	Bryant	1​	$ 4.29​

<tbody>
</tbody>

Sheet: Sheet5

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit

	B​
8​	=IFERROR(INDEX(Sheet6!M:M,SMALL(IF(LEFT(Sheet6!$C$3:$C$13,11)=Sheet5!$C$2,ROW(Sheet6!$C$3:$C$13)),ROW(1:1))),"")

Sheet: Sheet5

Excel 2016 (Windows) 32 bit

	C​
8​	=IFERROR(IF(OR(INDEX(Sheet6!$E$3:$E$13,MATCH(Sheet5!$B8,Sheet6!$M$3:$M$13,0))>$C$3,INDEX(Sheet6!$G$3:$G$13,MATCH(Sheet5!$B8,Sheet6!$M$3:$M$13,0))<$C$3),0,$C$3-MAX(INDEX(Sheet6!$E$3:$E$13,MATCH(Sheet5!$B8,Sheet6!$M$3:$M$13,0)),DATE(YEAR($C$3),MONTH($C$3),1))),"")​

<tbody>
</tbody>

Sheet: Sheet5

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit

	D​
8​	=($C$4-$C$5)/SUM($C$8:$C$18)*C8​

<tbody>
</tbody>

Sheet: Sheet5

<tbody>
</tbody>

Would love to see some other solutions if anyone has them.. Bump

 

[johnnytominaga]

Thanks iggy_ . Really appreciate your help.
I can come with a solution using Advanced filtering. Here's what I came up with just in case:

The user inputs:
- Total Bills Amount: [BILLS AMOUNT]
- Amount Already Paid: [AMOUNT PAID]
- Apartment: *[apartment name (without room number)]*
- Date: [INPUTTED DATE]

Once clicked, a macro button then filters by *[apartment name]*, Start Date<=[INPUTTED DATE], End Date>=[INPUTTED DATE]

I've then used the formula below to calculate the number of days to be counted from each guest:
=IFERROR(IF(MIN((([INPUTTED DATE])-(EOMONTH([INPUTTED DATE]-1;-1)+1));([END DATE]-(EOMONTH([INPUTTED DATE]-1;-1)+1));((EOMONTH([INPUTTED DATE];0))-[START DATE]);([INPUTTED DATE]-[START DATE]))<0;"";IF((MONTH([START DATE])&YEAR([START DATE])=MONTH([END DATE])&YEAR([END DATE]));([END DATE]-[START DATE]);MIN((([INPUTTED DATE])-(EOMONTH([INPUTTED DATE]-1;-1)+1));([END DATE]-(EOMONTH([INPUTTED DATE]-1;-1)+1));((EOMONTH([INPUTTED DATE];0))-[START DATE]);([INPUTTED DATE]-[START DATE]))));"")

And calculated the bills amount per room by simply summing up the total amount of days and multipling by the days per room:
=([BILLS AMOUNT]-[AMOUNT PAID])/SUM([ALL RESULTS FROM THE FORMULA ABOVE])*[RESULT FROM THE FORMULA ABOVE FOR EACH SPECIFIC ROOM]


Let me know if that makes sense.

Thanks again for your help.

 

[iggy_]

Sweet. Looks pretty similar, was just curious if someone had a cleaner solution, these giant formulas always give me a headache