Pro Rata Values

[harrycosh]

Hi

I am having difficulty in trying to Pro Rate values over a number of months. I have inserted a sample of my sheet below of what I am trying to achieve. At the moment I am manually inputting the Pro Rated Values.

<table x:str="" style="border-collapse: collapse; width: 923pt;" border="0" cellpadding="0" cellspacing="0" width="1224"><col style="width: 63pt;" width="84"> <col style="width: 67pt;" span="4" width="89"> <col style="width: 74pt;" span="8" width="98"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 63pt;" height="17" width="84">
</td> <td class="xl22" style="width: 67pt;" width="89">Inv Amount</td> <td class="xl22" style="width: 67pt;" width="89">Start</td> <td class="xl22" style="width: 67pt;" width="89">End</td> <td class="xl22" style="width: 67pt;" width="89">Total Days</td> <td class="xl23" style="width: 74pt;" width="98">
</td> <td class="xl23" style="width: 74pt;" width="98">
</td> <td class="xl23" style="width: 74pt;" width="98">
</td> <td class="xl23" style="width: 74pt;" width="98">
</td> <td class="xl23" style="width: 74pt;" width="98">
</td> <td class="xl23" style="width: 74pt;" width="98">
</td> <td class="xl23" style="width: 74pt;" width="98">
</td> <td class="xl23" style="width: 74pt;" width="98">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17">Period Start</td> <td class="xl31">
</td> <td class="xl31">
</td> <td class="xl31">
</td> <td class="xl31">
</td> <td class="xl31" x:num="40176">29/12/09</td> <td class="xl31" x:num="40205" x:fmla="=+F3+1">27/01/10</td> <td class="xl31" x:num="40234" x:fmla="=+G3+1">25/02/10</td> <td class="xl31" x:num="40270" x:fmla="=+H3+1">02/04/10</td> <td class="xl31" x:num="40299" x:fmla="=+I3+1">01/05/10</td> <td class="xl31" x:num="40328" x:fmla="=+J3+1">30/05/10</td> <td class="xl31" x:num="40364" x:fmla="=+K3+1">05/07/10</td> <td class="xl31" x:num="40400" x:fmla="=+L3+1">10/08/10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17">Period End</td> <td class="xl31">
</td> <td class="xl31">
</td> <td class="xl31">
</td> <td class="xl31">
</td> <td class="xl31" x:num="40204" x:fmla="=+F2+F4">26/01/10</td> <td class="xl31" x:num="40233" x:fmla="=+G2+G4">24/02/10</td> <td class="xl31" x:num="40269" x:fmla="=+H2+H4">01/04/10</td> <td class="xl31" x:num="40298" x:fmla="=+I2+I4">30/04/10</td> <td class="xl31" x:num="40327" x:fmla="=+J2+J4">29/05/10</td> <td class="xl31" x:num="40363" x:fmla="=+K2+K4">04/07/10</td> <td class="xl31" x:num="40399" x:fmla="=+L2+L4">09/08/10</td> <td class="xl31" x:num="40428" x:fmla="=+M2+M4">07/09/10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl30" style="height: 12.75pt;" height="17">Period Days</td> <td class="xl32">
</td> <td class="xl32">
</td> <td class="xl32">
</td> <td class="xl32">
</td> <td class="xl32" x:num="">28</td> <td class="xl32" x:num="">28</td> <td class="xl32" x:num="">35</td> <td class="xl32" x:num="">28</td> <td class="xl32" x:num="">28</td> <td class="xl32" x:num="">35</td> <td class="xl32" x:num="">35</td> <td class="xl32" x:num="">28</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25" x:num="">3601.12</td> <td class="xl26" x:num="40269">01/04/2010</td> <td class="xl26" x:num="40633"> 31/03/2011</td> <td class="xl27" x:num="" x:fmla="=+D5-C5+1"> 365</td> <td class="xl28">
</td> <td class="xl28">
</td> <td class="xl28" x:num="9.866082191780821" x:fmla="=B5*(1/E5)">9.87</td> <td class="xl28" x:num="276.250301369863" x:fmla="=$B$5*(I4/$E$5)">276.25</td> <td class="xl28" x:num="276.250301369863" x:fmla="=$B$5*(J4/$E$5)">276.25</td> <td class="xl28" x:num="345.31287671232872" x:fmla="=$B$5*(K4/$E$5)">345.31</td> <td class="xl28" x:num="345.31287671232872" x:fmla="=$B$5*(L4/$E$5)">345.31</td> <td class="xl28" x:num="276.250301369863" x:fmla="=$B$5*(M4/$E$5)">276.25</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25" x:num="">24000</td> <td class="xl26" x:num="40269">01/04/2010</td> <td class="xl26" x:num="40999"> 31/03/2012</td> <td class="xl27" x:num="" x:fmla="=+D6-C6+1"> 731</td> <td class="xl28">
</td> <td class="xl28">
</td> <td class="xl28">
</td> <td class="xl28">
</td> <td class="xl28">
</td> <td class="xl28">
</td> <td class="xl28">
</td> <td class="xl28">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25" x:num="">256.39</td> <td class="xl26" x:num="40270" x:fmla="=+D7+1">02/04/2010</td> <td class="xl26" x:num="40269" x:fmla="=+C6"> 01/04/2010</td> <td class="xl27" x:num="" x:fmla="=+D7-C7+1"> 0</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl25" x:num="">654.59</td> <td class="xl26" x:num="40299">01/05/2010</td> <td class="xl26" x:num="40310"> 12/05/2010</td> <td class="xl27" x:num="" x:fmla="=+D8-C8+1"> 12</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> <td class="xl25">
</td> </tr> </tbody></table>


Thanks

 

[Robert Mika]

Can you explain this leyout a little bit more?
What you expect to get?

 

[harrycosh]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1		Inv Amount	Start	End	Total Days			1	28	28	35	35	28	28	28	35	28	28	35	28	28	35	28	28	35	28	28	35	28	28	35	7
2	Period Start					29/12/09	27/01/10	25/02/10	02/04/10	01/05/10	30/05/10	05/07/10	10/08/10	08/09/10	07/10/10	05/11/10	11/12/10	09/01/11	07/02/11	15/03/11	13/04/11	12/05/11	17/06/11	16/07/11	14/08/11	19/09/11	18/10/11	16/11/11	22/12/11	20/01/12	18/02/12	25/03/12	23/04/12
3	Period End					26/01/10	24/02/10	01/04/10	30/04/10	29/05/10	04/07/10	09/08/10	07/09/10	06/10/10	04/11/10	10/12/10	08/01/11	06/02/11	14/03/11	12/04/11	11/05/11	16/06/11	15/07/11	13/08/11	18/09/11	17/10/11	15/11/11	21/12/11	19/01/12	17/02/12	24/03/12	22/04/12	21/05/12
4	Period Days					28	28	35	28	28	35	35	28	28	28	35	28	28	35	28	28	35	28	28	35	28	28	35	28	28	35	28	28
5		3601.12	01/04/2010	31/03/2011	365			9.87	276.25	276.25	345.31	345.31	276.25	276.25	276.25	345.31	276.25	276.25	345.31	276.25
6	23244.87	24000	01/04/2010	31/03/2012	731			32.83	919.29	919.29	1149.11	1149.11	919.29	919.29	919.29	1149.11	919.29	919.29	1149.11	919.29	919.29	1149.11	919.29	919.29	1149.11	919.29	919.29	1149.11	919.29	919.29	1149.11	229.82
7	755.13	256.39	02/04/2010	01/04/2010	0
Sheet1

I hope this makes more sense. I am trying to apportion the value for the days that it falls under in the period. If the value falls under a whole period then all the days in the period should be apportioned a value, and if it falls on part of a period then only part of the value should be apportioned depending on how many days it falls on.

 

[harrycosh]

I have inserted an updated version of my sheet. I hope it makes things clearer. Also I would like the sheet to be clever enough to realise that the end date of an invoice is before the start date and so not apportion anything.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Inv Amount	Start	End	Total Days	Calcualtions
2					Period Start	01/04/10	29/04/10	27/05/10	Calculated Total	Difference
3					Period End	28/04/10	26/05/10	30/06/10
4					Period Days	28	28	35
5	3,601.12	01/04/2010	30/06/2010	91		1,108.04	1,108.04	1,385.05	3,601.12	0.00
6	24,000.00	01/04/2010	30/04/2010	30		22,400.00	1,600.00		24,000.00	0.00
7	256.39	15/04/2010	15/06/2010	62		62.03	115.79	78.57	256.39	0.00
8	654.59	01/05/2010	30/04/2010	-1					0.00	654.59
Sheet1

Thanks in advance

 

[harrycosh]

Can anyone help?