Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
show daily sales as a percentage of monthly sales target
How can I show daily sales as a percentage of monthly sales target?
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
show daily sales as a percentage of monthly sales target
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. -- Gary''s Student - gsnu200713 "Max Bialystock" wrote: How can I show daily sales as a percentage of monthly sales target? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
show daily sales as a percentage of monthly sales target
Thank you very much for your help.
Your contribution is most valuable. Thank you. MB "Gary''s Student" wrote in message ... 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. -- Gary''s Student - gsnu200713 "Max Bialystock" wrote: How can I show daily sales as a percentage of monthly sales target? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Daily Sales | Excel Programming | |||
Help monthly sales by person | New Users to Excel | |||
track daily sales | Excel Discussion (Misc queries) | |||
I need a daily sales sheet to pull from monthly figures sheet | Excel Worksheet Functions |