Remember Me?

#1
October 19th 06, 10:48 PM posted to microsoft.public.excel.misc
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 16
Total Not Summing Correctly on Calculated Fields in Pivot Table

I have a spreadsheet showing Total Sales for Rep and Quarterly Quota.
I am also calculating number of weeks remaining in each quarter. When
remaining weeks = 1 I set the value to 1. In the Pivot Table I'm
showing what the Quarterly Quota is and the Total Sales per Quarter.
Then I have two calculated fields:

Difference (Quota - Total Sales) per quarter

Sales Per Week (Difference / Weeks-Left) This is sales needed per week
to meet quota

Everything works great except Total Sum of Sales Per Week not summing
correctly.
Should be \$810 + 180.60 = \$991.55.
I am assuming I can create a calculated field from another calculated
field but maybe that's my problem.

Total
Date Rep Item Sales Quota Weeks-Left
-----------------------------------------------------------------------------------------------------------------------------------
1/1/2005 Jones \$1,000.00 1
4/1/2005 Jones \$1,200.00 2
7/1/2005 Jones \$1,400.00 0
10/1/2005 Jones \$1,600.00 0
1/6/2005 Jones Pencil \$189.05
4/1/2005 Jones Binder \$299.40
6/8/2005 Jones Binder \$539.40
8/15/2005 Jones Pencil \$174.65
9/18/2005 Jones Pen Set \$255.84
10/2/2005 Jones Pen \$575.36
2/18/2006 Jones Binder \$19.96
7/4/2006 Jones Pen Set \$309.38

PIVOT TABLE:

Rep
Years Date Data Jones Grand Total
---------------------------------------------------------------------------------------------------------------------------------
2006 Qtr1 Sum of Quota \$1,000.00 \$1,000.00
Sum of Total Sales \$189.05 \$189.05
Sum of Difference \$810.95 \$810.95
Sum of Sales Per Week \$810.95 \$810.95
----------------------------------------------------------------------------------------------------------------------------------
Qtr2 Sum of Quota \$1,200.00 \$1,200.00
Sum of Total Sales \$838.80 \$838.80
Sum of Difference \$361.20 \$361.20
Sum of Sales Per Week \$180.60 \$180.60
-----------------------------------------------------------------------------------------------------------------------------------
Total Sum of Quota \$2,200.00 \$2,200.00
Total Sum of Total Sales \$1,027.85 \$1,027.85
Total Sum of Difference \$1,172.15 \$1,172.15
Total Sum of Sales Per Week \$390.72 \$390.72

Any ideas?

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Dominic Excel Discussion (Misc queries) 1 April 26th 06 11:50 PM Bloke in Excel Trouble Excel Worksheet Functions 2 April 16th 06 09:46 AM David Excel Discussion (Misc queries) 1 January 6th 06 03:56 AM Dino Excel Discussion (Misc queries) 3 November 2nd 05 09:43 PM Adam Excel Discussion (Misc queries) 6 March 24th 05 02:35 PM

All times are GMT +1. The time now is 06:09 PM.