Problem with Calculated Fields in a Pivot Table

[JimRoy]

I apologize in advance if this questions has already been asked or I am improperly posting. This is one of my first posts so hang with me please.....

I have a very simple excel pivot table with three columns:

Name	Actual Hrs	Max Hrs
Smith, Bill	42.00	40.00
White, Betty	39.00	40.00

I wish to add a calculated field to display Over Time hours. I have followed the instructions to the add a calculated field but I get this error message. What the heck am I doing wrong?

 

[Cubist]

I don't see 'Hours in your field list.

 

[JimRoy]

I don't see 'Hours in your field list.

Thats what is kind of strange. When I highlight "HoursActual" and then select "Insert", in the formula field it displays 'Hours and not HoursActual. Also note the apostrophe in front of "'Hours". I have tried to manually type in HoursActual but I get the same error message.

 

[Cubist]

Thats what is kind of strange. When I highlight "HoursActual" and then select "Insert", in the formula field it displays 'Hours and not HoursActual. Also note the apostrophe in front of "'Hours". I have tried to manually type in HoursActual but I get the same error message.

I think you want Calculated "Item" not "Field".

 

[JimRoy]

I think you want Calculated "Item" not "Field".

Calculated Item is greyed out.

 

[Cubist]

Calculated Item is greyed out.

Click on the header of the pivot table first.

 

[JimRoy]

Click on the header of the pivot table first.

Clicked on the header and was able to access Calculated Item, but it would not allow the use of an "Average" in the value so I tried to just type in 40 in the formula and get same error message and still seeing 'Hours instead of HoursActual.

 

[JimRoy]

This seems so darn basic. Just a simple subraction formula

 

[Cubist]

hmm. What does your data look like? You can do a mock-up with non-sensitive info. I want to see the names of your headers and the items within each column.

 

[JimRoy]

Well I just resolved the problem. I went back to the original table and changed the name of the HoursActual column header to just Hrs and bingo. Problem resolved! Go figure?