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
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