Pivot Table Calculated Fields

S

Slim Slender

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?
 
L

Leung

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 ?
 
S

Slim Slender

To create a Calculated Field in Excel 2003: with the cursor in the
pivot table, click on the Pivot Table drop down list on the Pivot
Table tool bar. Then click on Formulas, then Calculated Field.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top