Calculate split bills considering variable occupation and multiple rooms

johnnytominaga

New Member
Joined
Apr 27, 2018
Messages
19
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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Can you share a sample of your database and the result you would like to see.

Hey!

Here we go:

Database:
fZieyc

fZieyc

https://ibb.co/fZieyc

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


Let me know if that helps.

Thanks a lot.
 
Upvote 0
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
[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]
B
[/TH]
[TH]
C
[/TH]
[TH]
D
[/TH]
[TH]
E
[/TH]
[TH]
F
[/TH]
[TH]
G
[/TH]
[TH]
H
[/TH]
[TH]
I
[/TH]
[TH]
J
[/TH]
[TH]
K
[/TH]
[TH]
L
[/TH]
[TH]
M
[/TH]
[TH]
N
[/TH]
[TH]
O
[/TH]
[TH]
P
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
2
[/TD]
[TD]ID[/TD]
[TD]Room[/TD]
[TD][/TD]
[TD]Start[/TD]
[TD]Day[/TD]
[TD]End[/TD]
[TD]Status[/TD]
[TD]Cost per month[/TD]
[TD]Deposit[/TD]
[TD]Agency Fee[/TD]
[TD]Total Cost[/TD]
[TD]Full Name[/TD]
[TD]Nationality[/TD]
[TD]Email[/TD]
[TD]Phone[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
3
[/TD]
[TD]
1​
[/TD]
[TD]Apartment 1 -Room 1[/TD]
[TD]Room 1[/TD]
[TD]
4/27/2018​
[/TD]
[TD]
101​
[/TD]
[TD]
8/6/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
4
[/TD]
[TD]
2​
[/TD]
[TD]Apartment 1[/TD]
[TD]Room 2[/TD]
[TD]
5/5/2018​
[/TD]
[TD]
11​
[/TD]
[TD]
5/16/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Byron[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
5
[/TD]
[TD]
3​
[/TD]
[TD]Apartment 1[/TD]
[TD]Room 3[/TD]
[TD]
1/1/2018​
[/TD]
[TD]
135​
[/TD]
[TD]
5/16/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Sung[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
6
[/TD]
[TD]
4​
[/TD]
[TD]Apartment 1[/TD]
[TD]Room 4[/TD]
[TD]
2/22/2018​
[/TD]
[TD]
128​
[/TD]
[TD]
6/30/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Elton[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
7
[/TD]
[TD]
5​
[/TD]
[TD]Apartment 2[/TD]
[TD]Room 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
8
[/TD]
[TD]
6​
[/TD]
[TD]Apartment 3[/TD]
[TD]Room 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
9
[/TD]
[TD]
7​
[/TD]
[TD]Apartment 4[/TD]
[TD]Room 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
10
[/TD]
[TD]
8​
[/TD]
[TD]Apartment 4[/TD]
[TD]Room 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
11
[/TD]
[TD]
9​
[/TD]
[TD]Apartment 4[/TD]
[TD]Room 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
12
[/TD]
[TD]
10​
[/TD]
[TD]Apartment 1[/TD]
[TD]Room 2[/TD]
[TD]
5/19/2018​
[/TD]
[TD]
37​
[/TD]
[TD]
6/25/2018​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Tillie[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
13
[/TD]
[TD]
11​
[/TD]
[TD]Apartment 1[/TD]
[TD]Room 3[/TD]
[TD]
5/17/2018​
[/TD]
[TD]
323​
[/TD]
[TD]
4/5/2019​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bryant[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet6[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]
B
[/TH]
[TH]
C
[/TH]
[TH]
D
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
2
[/TD]
[TD]Property[/TD]
[TD]Apartment 1[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
3
[/TD]
[TD]Date[/TD]
[TD]
5/18/2018​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
4
[/TD]
[TD]Total Bill Amount[/TD]
[TD]
200​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
5
[/TD]
[TD]Amount Paid[/TD]
[TD]
50​
[/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
7
[/TD]
[TD]Amount per Person[/TD]
[TD]Days Owed[/TD]
[TD]Amount Owed[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
8
[/TD]
[TD]Joe[/TD]
[TD]
17​
[/TD]
[TD]
$ 72.86​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
9
[/TD]
[TD]Byron[/TD]
[TD]
0​
[/TD]
[TD]
$ -​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
10
[/TD]
[TD]Sung[/TD]
[TD]
0​
[/TD]
[TD]
$ -​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
11
[/TD]
[TD]Elton[/TD]
[TD]
17​
[/TD]
[TD]
$ 72.86​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
12
[/TD]
[TD]Tillie[/TD]
[TD]
0​
[/TD]
[TD]
$ -​
[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
13
[/TD]
[TD]Bryant[/TD]
[TD]
1​
[/TD]
[TD]
$ 4.29​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet5[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
B
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8
[/td][td]=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))),"")[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet5[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]
C
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
8
[/TD]
[TD]
=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))),"")​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet5[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]
D
[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]
8
[/TD]
[TD]
=($C$4-$C$5)/SUM($C$8:$C$18)*C8​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet5[/TD]
[/TR]
</tbody>[/TABLE]



Would love to see some other solutions if anyone has them.. Bump
 
Last edited:
Upvote 0
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.
 
Upvote 0
Sweet. Looks pretty similar, was just curious if someone had a cleaner solution, these giant formulas always give me a headache :)
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,707
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top