Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calculated Fields
Here is a sample of my data:
Date Employee Hours 6/1/2009 Steve 8.00 6/1/2009 Jack 5.50 6/1/2009 Steve 1.50 6/1/2009 Jack 1.50 6/1/2009 Mike 8.50 6/1/2009 Jack 2.50 6/2/2009 Steve 8.00 6/2/2009 Jack 7.00 6/2/2009 Mike 4.50 6/2/2009 Steve 1.50 6/2/2009 Jack 1.00 6/2/2009 Mike 4.50 6/3/2009 Steve 8.00 6/3/2009 Mike 8.00 6/3/2009 Jack 6.50 6/3/2009 Steve 1.50 6/3/2009 Mike 1.00 6/3/2009 Jack 3.00 6/4/2009 Steve 8.50 6/4/2009 Mike 9.50 6/4/2009 Jack 8.00 6/4/2009 Steve 1.50 6/4/2009 Jack 1.50 6/5/2009 Steve 8.00 6/5/2009 Jack 8.00 6/5/2009 Mike 6.50 6/5/2009 Mike 5.00 I created two Calculated Fields for a Pivot Table: Calculated Field Solve Order Field Formula 1 Regular Time =IF(Hours 40,40,Hours) 2 Overtime =IF(Hours 40,Hours-40,0) Here is the Pivot Table: Employee Data Total Jack Sum of Regular Time 40.00 Sum of Overtime 4.50 Sum of Hours 44.50 Mike Sum of Regular Time 40.00 Sum of Overtime 7.50 Sum of Hours 47.50 Steve Sum of Regular Time 40.00 Sum of Overtime 6.50 Sum of Hours 46.50 Total Sum of Regular Time 40.00 Total Sum of Overtime 98.50 Total Sum of Hours 138.50 All figures for the individuals are correct and the Total Sum of Hours at the bottom is correct, but the Total Sum of Regular Time s/b 120.00 and the Total Sum of Overtime s/b 18.50. What am I doing wrong? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table Calculated Fields
Hi can i know how to created two Calculated Fields in the pivot table? so you add to the pivot table directly rather than adding 2 columne ? "Slim Slender" wrote: Here is a sample of my data: Date Employee Hours 6/1/2009 Steve 8.00 6/1/2009 Jack 5.50 6/1/2009 Steve 1.50 6/1/2009 Jack 1.50 6/1/2009 Mike 8.50 6/1/2009 Jack 2.50 6/2/2009 Steve 8.00 6/2/2009 Jack 7.00 6/2/2009 Mike 4.50 6/2/2009 Steve 1.50 6/2/2009 Jack 1.00 6/2/2009 Mike 4.50 6/3/2009 Steve 8.00 6/3/2009 Mike 8.00 6/3/2009 Jack 6.50 6/3/2009 Steve 1.50 6/3/2009 Mike 1.00 6/3/2009 Jack 3.00 6/4/2009 Steve 8.50 6/4/2009 Mike 9.50 6/4/2009 Jack 8.00 6/4/2009 Steve 1.50 6/4/2009 Jack 1.50 6/5/2009 Steve 8.00 6/5/2009 Jack 8.00 6/5/2009 Mike 6.50 6/5/2009 Mike 5.00 I created two Calculated Fields for a Pivot Table: Calculated Field Solve Order Field Formula 1 Regular Time =IF(Hours 40,40,Hours) 2 Overtime =IF(Hours 40,Hours-40,0) Here is the Pivot Table: Employee Data Total Jack Sum of Regular Time 40.00 Sum of Overtime 4.50 Sum of Hours 44.50 Mike Sum of Regular Time 40.00 Sum of Overtime 7.50 Sum of Hours 47.50 Steve Sum of Regular Time 40.00 Sum of Overtime 6.50 Sum of Hours 46.50 Total Sum of Regular Time 40.00 Total Sum of Overtime 98.50 Total Sum of Hours 138.50 All figures for the individuals are correct and the Total Sum of Hours at the bottom is correct, but the Total Sum of Regular Time s/b 120.00 and the Total Sum of Overtime s/b 18.50. What am I doing wrong? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Calculated Fields | Charts and Charting in Excel | |||
pivot table calculated fields | Excel Discussion (Misc queries) | |||
Pivot Table Calculated Fields | Excel Discussion (Misc queries) | |||
Pivot table and calculated fields | Excel Discussion (Misc queries) | |||
Pivot Table: Calculated Fields | Excel Programming |