I'm trying to come up with a cell formula to calculate monthly costs going into the future like so:
<table style="width: 450px; height: 360px;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:86pt" height="20" width="114">Date of Change</td> <td colspan="2" style="mso-ignore:colspan;width:99pt" width="132">Cost per day</td> <td style="width:48pt" width="64">Month</td> <td style="width:48pt" width="64">Cost Per Month</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">December 1, 2011</td> <td class="xl67" align="right">$1,080.00</td> <td>
</td> <td colspan="2" style="mso-ignore:colspan">December</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">January 24, 2012</td> <td class="xl67" align="right">$1,024.00</td> <td>
</td> <td>January</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">February 3, 2012</td> <td class="xl67" align="right">$945.00</td> <td>
</td> <td>February</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">March 6, 2012</td> <td class="xl67" align="right">$945.00</td> <td>
</td> <td>March</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">March 21, 2012</td> <td class="xl67" align="right">$540.00</td> <td>
</td> <td>April</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">May 30, 2012</td> <td class="xl67" align="right">$540.00</td> <td>
</td> <td>May</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">June 25, 2012</td> <td class="xl67" align="right">$270.00</td> <td>
</td> <td>June</td> <td>
</td> </tr> </tbody> </table>
The Cost Per Day changes to $1080.00 on December 1st, and remains that way until January 24th, when it changes to $1024.00, etc...
I am trying to avoid writing a custom formula for the "Cost Per Month" column to account for how many days to calculate at which rate. Is there a way to make an auto-fillable formula that will figure out how many days (workdays) to use at each rate?
<table style="width: 450px; height: 360px;" border="0" cellpadding="0" cellspacing="0"> <colgroup><col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2486;width:51pt" width="68"> <col style="width:48pt" span="2" width="64"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:86pt" height="20" width="114">Date of Change</td> <td colspan="2" style="mso-ignore:colspan;width:99pt" width="132">Cost per day</td> <td style="width:48pt" width="64">Month</td> <td style="width:48pt" width="64">Cost Per Month</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">December 1, 2011</td> <td class="xl67" align="right">$1,080.00</td> <td>
</td> <td colspan="2" style="mso-ignore:colspan">December</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">January 24, 2012</td> <td class="xl67" align="right">$1,024.00</td> <td>
</td> <td>January</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">February 3, 2012</td> <td class="xl67" align="right">$945.00</td> <td>
</td> <td>February</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">March 6, 2012</td> <td class="xl67" align="right">$945.00</td> <td>
</td> <td>March</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">March 21, 2012</td> <td class="xl67" align="right">$540.00</td> <td>
</td> <td>April</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt" height="20">May 30, 2012</td> <td class="xl67" align="right">$540.00</td> <td>
</td> <td>May</td> <td>
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">June 25, 2012</td> <td class="xl67" align="right">$270.00</td> <td>
</td> <td>June</td> <td>
</td> </tr> </tbody> </table>
The Cost Per Day changes to $1080.00 on December 1st, and remains that way until January 24th, when it changes to $1024.00, etc...
I am trying to avoid writing a custom formula for the "Cost Per Month" column to account for how many days to calculate at which rate. Is there a way to make an auto-fillable formula that will figure out how many days (workdays) to use at each rate?
