Houseshare Pro Rata Calculator

[jasebruv123]

Hi there!

I'm very new to excel, and I'm hoping someone could provide the spreadsheet formula which will calculate the below pro rata for each person:

I require a spreadsheet to calculate the total bill amount ($336.70) split between six people. I'm using day rates between a bill period of 174 days. Some people haven't been living with me for the entire 174 day bill period and I'm needing to calculate the following:

How much the following six people living in the house-share owe:
Person 1: Pro rata 174 days of the house's total owing bill amount ($336.70) over the 174 day bill period
Person 2: Pro rata 174 days of the house's total owing bill amount ($336.70) over the 174 day bill period
Person 3: Pro rata 162 days of the house's total owing bill amount ($336.70) over the 174 day bill period
Person 4: Pro rata 143 days of the house's total owing bill amount ($336.70) over the 174 day bill period
Person 5: Pro rata 74 days of the house's total owing bill amount ($336.70) over the 174 day bill period
Person 6: Pro rata 41 days of the house's total owing bill amount ($336.70) over the 174 day bill period

Can anyone please assist?

Thanks in advance!

 

[kweaver]

Re: [HELP] Houseshare Pro Rata Calculator Needed

<tbody>
[TD="class: xl69, colspan: 2"][/TD]

</tbody>

 

[kweaver]

Re: [HELP] Houseshare Pro Rata Calculator Needed

Last post got messed up.

I put the 336.70 in L1 on my sheet.
Then, the total in L8 and the formula in M2 which was filled down to M7

Hope this is what you wanted.

[TABLE="width: 318"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]336.70[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD="align: right"]76.284[/TD]
[TD] ← $L$1*L2/$L$8[/TD]
[/TR]
[TR]
[TD="align: right"]174[/TD]
[TD="align: right"]76.284[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]162[/TD]
[TD="align: right"]71.023[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]143[/TD]
[TD="align: right"]62.693[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]74[/TD]
[TD="align: right"]32.442[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]41[/TD]
[TD="align: right"]17.975[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]768[/TD]
[TD="colspan: 2"] ← SUM(L2:L7)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Re: [HELP] Houseshare Pro Rata Calculator Needed

This maybe

Excel 2013/2016
	A	B	C
1	total bil	$ 336.70	per person
2	person 1	174	$ 76.28
3	person 2	174	$ 76.28
4	person 3	162	$ 71.02
5	person 4	143	$ 62.69
6	person 5	74	$ 32.44
7	person 6	41	$ 17.97
8		768
Sheet1

Cell Formulas
Range	Formula
C2	=$B$1/$B$8*B2
C3	=$B$1/$B$8*B3
C4	=$B$1/$B$8*B4
C5	=$B$1/$B$8*B5
C6	=$B$1/$B$8*B6
C7	=$B$1/$B$8*B7
B8	=SUM(B2:B7)