Pivot Table Field based on two data area fields

[John Sweeney]

How do I created a calculated field in a pivot table based on two data area
fields. My pivot table has three fields:
Username
COUNT Number of Items
SUM Hours to Complete

I want to add the field AVERAGE TIME TO COMPLETE that is calculated as
=SUM Hours to Complete / COUNT Number of Items

 

[Luke M]

From the PivotTable toolbar, click PivotTable, Formulas, then calculated
field. Assign a name of "AVERAGE TIME TO COMPLETE"
Formula is:
=SUM(Hours to Complete)/COUNT(Number of Items)
Note that it's probably better to insert the Hours and Number using the
"insert field" button, so you get the exact formatting/name correct.

 

[Shane Devenshire]

Hi,

If I understand correctly there is no need to SUM or COUNT the items in the
formula because Excel has already down it for you, then

1. Place your cursor in a row field and choose Pivot Table, Formulas,
Calculated Field.
2. Name the new field and in the Formula box enter
='SUM Hours to Complete' / 'COUNT Number of Items'

 

[Shane Devenshire]

Hi,

I also might add that SUM('SUM Hours to Complete') returns the same results
as 'SUM Hours to Complete'.

This is at the core of our requests to Microsoft to allow us access to the
detail in the pivot table cache'. By the time we access the pivot table data
with a formula the data has already been summarized by the tools, so we don't
have access to the record by record detail. Although that doesn't impact
your problem is does impact other problems that can't be solved using the
pivot table, such as calculating the MEDIAN.