# Week / Date Formula Help

#### freddyatl

##### New Member
I am working on a forecasting spreadsheet for mailers. These mailers go out at different times, and I want to be able to forecast the projected response by week. For any mailer, Week 1 will forecast 3% return, Week 2 will forecast 40%, and so on. However, this is all based on when the mailer goes out.

Is there a formula to calculate the next week based on this mail date, and then use a formula to calculate the weekly repsonse rate? I've included a sample below. Mailer 1 has a Week 1 Date of 5/27, but Mailer 2 is June 3.

<TABLE style="WIDTH: 438pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=584 border=0><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=117 height=20>MAILER</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=83>In-Home Date</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Drop Qty</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>RR</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>27-May</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>3-Jun</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>10-Jun</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>17-Jun</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Intro Self-Mailer - 1T</TD><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>13-May</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 639 </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime">0.78%</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 2 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 1 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 0 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> - </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Mailer #2</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>21-May</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 640 </TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: lime">0.78%</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>2</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> 1 </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> - </TD></TR></TBODY></TABLE>

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Andrew Fergus

##### MrExcel MVP
Hi

I'm not sure I understand. Do you want to auto-populate each week with the expected number of responses? So if you had a 3rd mail-out of 1000 brochures on June 3rd, there would be no responses for the week beginning May 27th, but there would be 30 (3%) responses in the week beginning June 3rd, and 400 (40%) responses in the week beginning June 10th. Is that what you want?

Andrew

#### freddyatl

##### New Member
Yes, sort of. The only additional item is this response is a percentage of the overall expected response rate. So, the third mailer sent on June 3 of 1000. Overall, we only expect to get a 1% response rate, or 10. This is the total response, but week 1 3% of that, or .3, and 40% the next week or 4, and so on.

Does this help at all?

#### Andrew Fergus

##### MrExcel MVP
Hi

I think I got it to work using the following set-up. Please note I used a helper table for the expected weekly response %'s and also a helper row for the week numbers. Please refer to the formula in E4 - this can be copied down and across using the layout below.

<script language="JavaScript" src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></script><center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="11" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Mailer.xls</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: 11.0 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="11" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb059465"><input onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' value="Copy Formula" name="btCb290334" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0);" colspan="11" bgcolor="white"><table border="0"><tbody><tr><form name="formFb543420"></form><td style="width: 60px;" align="center" bgcolor="white"><select onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name="sltNb142651"><option value="=ROUND(IF(E\$2>=\$B4,\$C4*\$D4*(INDEX(\$B\$10:\$B\$17,1+E\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B4,\$E\$2:\$J\$2,1)))),0),0)" selected="selected">E4</option><option value="=ROUND(IF(F\$2>=\$B4,\$C4*\$D4*(INDEX(\$B\$10:\$B\$17,1+F\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B4,\$E\$2:\$J\$2,1)))),0),0)">F4</option><option value="=ROUND(IF(G\$2>=\$B4,\$C4*\$D4*(INDEX(\$B\$10:\$B\$17,1+G\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B4,\$E\$2:\$J\$2,1)))),0),0)">G4</option><option value="=ROUND(IF(H\$2>=\$B4,\$C4*\$D4*(INDEX(\$B\$10:\$B\$17,1+H\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B4,\$E\$2:\$J\$2,1)))),0),0)">H4</option><option value="=ROUND(IF(I\$2>=\$B4,\$C4*\$D4*(INDEX(\$B\$10:\$B\$17,1+I\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B4,\$E\$2:\$J\$2,1)))),0),0)">I4</option><option value="=ROUND(IF(J\$2>=\$B4,\$C4*\$D4*(INDEX(\$B\$10:\$B\$17,1+J\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B4,\$E\$2:\$J\$2,1)))),0),0)">J4</option><option value="=ROUND(IF(E\$2>=\$B5,\$C5*\$D5*(INDEX(\$B\$10:\$B\$17,1+E\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B5,\$E\$2:\$J\$2,1)))),0),0)">E5</option><option value="=ROUND(IF(F\$2>=\$B5,\$C5*\$D5*(INDEX(\$B\$10:\$B\$17,1+F\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B5,\$E\$2:\$J\$2,1)))),0),0)">F5</option><option value="=ROUND(IF(G\$2>=\$B5,\$C5*\$D5*(INDEX(\$B\$10:\$B\$17,1+G\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B5,\$E\$2:\$J\$2,1)))),0),0)">G5</option><option value="=ROUND(IF(H\$2>=\$B5,\$C5*\$D5*(INDEX(\$B\$10:\$B\$17,1+H\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B5,\$E\$2:\$J\$2,1)))),0),0)">H5</option><option value="=ROUND(IF(I\$2>=\$B5,\$C5*\$D5*(INDEX(\$B\$10:\$B\$17,1+I\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B5,\$E\$2:\$J\$2,1)))),0),0)">I5</option><option value="=ROUND(IF(J\$2>=\$B5,\$C5*\$D5*(INDEX(\$B\$10:\$B\$17,1+J\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B5,\$E\$2:\$J\$2,1)))),0),0)">J5</option><option value="=ROUND(IF(E\$2>=\$B6,\$C6*\$D6*(INDEX(\$B\$10:\$B\$17,1+E\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B6,\$E\$2:\$J\$2,1)))),0),0)">E6</option><option value="=ROUND(IF(F\$2>=\$B6,\$C6*\$D6*(INDEX(\$B\$10:\$B\$17,1+F\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B6,\$E\$2:\$J\$2,1)))),0),0)">F6</option><option value="=ROUND(IF(G\$2>=\$B6,\$C6*\$D6*(INDEX(\$B\$10:\$B\$17,1+G\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B6,\$E\$2:\$J\$2,1)))),0),0)">G6</option><option value="=ROUND(IF(H\$2>=\$B6,\$C6*\$D6*(INDEX(\$B\$10:\$B\$17,1+H\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B6,\$E\$2:\$J\$2,1)))),0),0)">H6</option><option value="=ROUND(IF(I\$2>=\$B6,\$C6*\$D6*(INDEX(\$B\$10:\$B\$17,1+I\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B6,\$E\$2:\$J\$2,1)))),0),0)">I6</option><option value="=ROUND(IF(J\$2>=\$B6,\$C6*\$D6*(INDEX(\$B\$10:\$B\$17,1+J\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B6,\$E\$2:\$J\$2,1)))),0),0)">J6</option><option value="=SUM(B10:B17)">B18</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="=ROUND(IF(E\$2>=\$B4,\$C4*\$D4*(INDEX(\$B\$10:\$B\$17,1+E\$1-INDEX(\$E\$1:\$J\$1,MATCH(6+\$B4,\$E\$2:\$J\$2,1)))),0),0)" name="txbFb965317"></td></tr></tbody></table></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); border-right: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="center" width="2%">
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

Andrew