View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Herbert Seidenberg Herbert Seidenberg is offline
external usenet poster
 
Posts: 1,180
Default 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