Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Sales Projections column
I would like to add a column for projected sales into a pivot table.
Formula would be Grand Total/(month sales are through)*12. I am running into problems because when I try to add the column on top because I have the MONTH column on top already the pivot table wants to expand beyond 255 columns. Here is what I want it to look like: MONTH SALES REP NAME Projected Sales Jan Feb March..... Grand Total John Smith $5,600 $200 $500 $700 $1,400 Bruce Jones Steve Johnson Any Suggestions? Also, the pivot table format I am using is the default one. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Sales Projections column
Assume your PT looks like this:
SalesRep 1 2 3 4 5 6 7 AmTh 12 14 13 17 22 19 BrKe 44 37 35 39 30 29 CaTo 17 22 19 11 Some month sales are blank, not zero, because the sales rep might have been absent. These months should not be included in the average. Also, some reps might have an up or down trend. Thus I suggest a linear trendline, not an average. Instead of month text labels, use numbers. If the PT is located at G3, then use this formula outside the PT for projected yearly total sales: =SUM(FORECAST({1,2,3,4,5,6,7,8,9,10,11,12},G5:N5,$ G$4:$N$4)) =238 =333 =182 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table Sales Projections column
On Jan 4, 7:46*pm, Herbert Seidenberg wrote:
Assume your PT looks like this: SalesRep * * * *1 * * * 2 * * * 3 * * * 4 * * * 5 * * * 6 * * * 7 AmTh * *12 * * *14 * * *13 * * * * * * *17 * * *22 * * *19 BrKe * *44 * * *37 * * *35 * * *39 * * *30 * * *29 CaTo * * * * * * * * * * * * * *17 * * *22 * * *19 * * *11 Some month sales are blank, not zero, because the sales rep might have been absent. These months should not be included in the average. Also, some reps might have an up or down trend. Thus I suggest a linear trendline, not an average. Instead of month text labels, use numbers. If the PT is located at G3, then use this formula outside the PT for projected yearly total sales: =SUM(FORECAST({1,2,3,4,5,6,7,8,9,10,11,12},G5:N5,$ G$4:$N$4)) =238 =333 =182 works great, thanks Herbert! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting a Pivot Table Column that is not the first column... | Excel Worksheet Functions | |||
pivot table, how to add 1 column only | Excel Discussion (Misc queries) | |||
Add Sales Goals to Sales Report in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot table for reporting sales performance | Excel Discussion (Misc queries) | |||
Pivot Table - report product that have sales above defined level | Excel Discussion (Misc queries) |