Multiple formulas in Pivot Table
 

I am trying to get a pivot table that will perform three of the stock
operations (sum, count and average) on the same data set, and have the
results as three different rows.

I have had a play around with Calculated Fields, and whilst I can add them,
I cannot change the Field Setting (greyed out).

Is there any way around this?

Multiple formulas in Pivot Table
 

I never try to manipulate data in pivot tables. Too much isn't allowed.

I would just copy the entire table using Paste Special, Paste Values. Then
you end up with a copy of the pivot table that you CAN manipulate.

"The Cherub" wrote:

I am trying to get a pivot table that will perform three of the stock
operations (sum, count and average) on the same data set, and have the
results as three different rows.

I have had a play around with Calculated Fields, and whilst I can add them,
I cannot change the Field Setting (greyed out).

Is there any way around this?

Multiple formulas in Pivot Table
 

Add the same data field to the pivot table, two more times.
In the pivot table, right-click on a cell in the second copy of the data
Click on Field Settings
Under Summarize by, click on Count, then click OK
Change the third copy of the data so it summarizes by Average.

Drag the Data field button to the Row area, so each copy of the data
field is in a separate row.

The Cherub wrote:
I am trying to get a pivot table that will perform three of the stock
operations (sum, count and average) on the same data set, and have the
results as three different rows.

I have had a play around with Calculated Fields, and whilst I can add them,
I cannot change the Field Setting (greyed out).

Is there any way around this?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com

Multiple formulas in Pivot Table
 

Perfect, thank you for that.

Whilst using copy / paste special would have been fine for a one off, this
is something that gets done weekly, so having it still in the Pivot Table is
vital.

Thanks Debra

"Debra Dalgleish" wrote:

Add the same data field to the pivot table, two more times.
In the pivot table, right-click on a cell in the second copy of the data
Click on Field Settings
Under Summarize by, click on Count, then click OK
Change the third copy of the data so it summarizes by Average.

Drag the Data field button to the Row area, so each copy of the data
field is in a separate row.

The Cherub wrote:
I am trying to get a pivot table that will perform three of the stock
operations (sum, count and average) on the same data set, and have the
results as three different rows.

I have had a play around with Calculated Fields, and whilst I can add them,
I cannot change the Field Setting (greyed out).

Is there any way around this?


--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com