This table arrangement should work for you:
First make a table of sales, say column A is the date and column B is the
daily sales amount. This table will grow day-by-day:
4/3/2007 $1,234.54
4/4/2007 $1,266.54
4/5/2007 $1,335.54
4/6/2007 $1,405.54
4/7/2007 $1,467.54
Then create a monthly sales forecast table (say columns G & H):
1 $37,036.20
2 $37,776.92
3 $38,532.46
4 $39,303.11
5 $40,089.17
6 $40,890.96
7 $41,708.78
8 $42,542.95
9 $43,393.81
10 $44,261.69
11 $45,146.92
12 $46,049.86
With ony estimate for each month.
Finally in column C enter:
=B1/VLOOKUP(MONTH(A1),F$1:G$12,2,1) and format as a percent
and copy down.
For our example:
4/3/2007 $1,234.54 3.14%
4/4/2007 $1,266.54 3.22%
4/5/2007 $1,335.54 3.40%
4/6/2007 $1,405.54 3.58%
4/7/2007 $1,467.54 3.73%
Explanation:
The formula gets the date and calculates the month. It looks up the month
in the GH table and get the forecast for that mon and the gets the percentage
from that.
