Pivot Table - Adding Average as calculated field

**Matt1234au** · 12-22-2009, 07:34 PM

Hi

I have attached a simple Pivot file. Basically my data is some ratios, by year by stock.

BHP OST
Ratio 2008 2009 2008 2009
ARatio 2 2.1 3 2.9
BRatio 1.1 1.4 1.5 1.6

I want calculate the average of the ratios by year (but not by stock) and I don't want to use the Total Column/Row function as I really want the averages on the left of the table so I can split the screen as I scroll across.

So I was hoping on some result that looked something like:

AVERAGES BHP OST
Ratio 2008 2009 2008 2009 2008 2009
ARatio 08Avg 09Avg 2 2.1 3 2.9
BRatio 08Avg 09Avg 1.1 1.4 1.5 1.6

So I am playing around with calculated fields but just can't get the right outcome.

Any help greatly appreciated

Thanks

Matt

**Palmetto** · 12-22-2009, 08:12 PM

No need for a calculated field. See attached.
You can set the field item to show the Average.

I've arrange the field items to provide the layout you showed and set the Sum of Ratios to show the Average.

**Matt1234au** · 12-22-2009, 08:24 PM

Palmetto

Thanks for your reply

I don't think I was really clear in my question. I want the average of the ratios per year across all the stocks.

I have attached an updated file that shows how it can be done by using Field Settings Average (just like you did) - but this puts the average way down on the right. I really want the average up on the left.

Can that be done?

Thanks

**Palmetto** · 12-22-2009, 08:33 PM

Pivot Tables are powerful, but one of downside is their limit flexibility in arranging data exactly as you want.

In your case, the PT I gave you is as close as you're going to get. So, in short, the way you want to present the data cannot be done in a Pivot Table

Take look at using the DAVERAGE function which can take criteria and you can use any cell arrangement you want.

**Matt1234au** · 12-22-2009, 08:50 PM

Thanks Palmetto

I think I'll use DAVERAGE within a PivotTable Copy and will write a bit of VB that copys the table and inserts the average

thanks again