Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 19th 06, 10:48 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
Posts: 16
Default 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.


SREADSHEET:
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?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pivot Table Calculated Item Dominic Excel Discussion (Misc queries) 1 April 26th 06 11:50 PM
Can I use IF function in calculated fields in a pivot table? Bloke in Excel Trouble Excel Worksheet Functions 2 April 16th 06 09:46 AM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 03:56 AM
Pivot table # of fields Dino Excel Discussion (Misc queries) 3 November 2nd 05 09:43 PM
Pivot Tables, Help? Adam Excel Discussion (Misc queries) 6 March 24th 05 02:35 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017