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!
|