Staggered rent calculation.

[Tmwahomas]

Hi,

Some friends of mine and I are staying in a villa for a set total cost £3780.

But 7 people will stay for the full 3 weeks,
1 person will stay for 2 weeks,
and 2 people will stay for 1.5 weeks.

How do I calculate how much each person has to pay?

 

[alansidman]

Excel Workbook
	A	B	C	D
1	3780	People	Days	TotDays
2		3	21	63
3		1	14	14
4		2	10	20
5				97
6	Cost per Day		38.96907
Sheet1

 

[shg]

I like Alan's approach. B2 should be 7.

 

[alansidman]

Stephen

Thanks for the correction.

Alan

 

[Tmwahomas]

Thats awesome - thank you so much Alan! That's helped me a lot.

Sheet1

*	A	B	C	D
People	Days	TotDays
*
*
*
*	*	*
Cost per Day	*	<--Each person pays per day

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]3780[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]63[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]97[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]38.96907[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D2 =B2*C2 D3 =B3*C3 D4 =B4*C4 D5 =SUM(D2:D4) C6 =A1/D5 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4