Hi,
I have a datasheet with many pivot tables that I use to analyse data.
In the datasheet each row has a "Character", "Brand" and a number of "SKU's"
as well as other data such as "Retailer", "Store" and "Date".
Everything works fine, BUT...

I want to calculate the average number of SKU's for a given "Brand" by
"Retailer"

For example, if I use 'Sum of SKU' I get an amount of 15 for "Rods City
Store" and 10 for "Rods East Mall" ("Rods" being the Retailer and "Rods City
Store" being a Store). That's fine.
But if I use 'Average of SKU' I get numbers 1.667 and 2 respectively.

What I want is an average for "Rods" of 12.5.

I could use a calculated field, but the nature of the pivot table means that
the number of columns changes, so I'm not sure how to write the formula.

Also, it would help if anyone can explai where those low averages are coming
from. Is it perhaps an average per character even though that is not the
criteria used in the pivot?

Thanks,

Graeme

Mmm,
I think I've found my own solution - however if anyone has a better
suggestion please let me know!

I have been thinking that the ideal is to use one large data set from which
to write all my pvot tables. (Forgive me but I work quite a lot with Access
and the mindset is to house large sets of data).

I see that in this scenario the 'Average' function works if I take
"Character" out of the spreadsheet. In otherwords, for this table I need to
create another table for my source data.

As I'm capturing large amounts of data each month in Access and then
analysing in Excel (Pivots in Excel are easier to work with from a
presentation point of view) this unfortunately means I have to dump data to
more than one worksheet.

But if that's how I have to do it I'll live with it.


"Graeme at Raptup" wrote:

Hi,
I have a datasheet with many pivot tables that I use to analyse data.
In the datasheet each row has a "Character", "Brand" and a number of "SKU's"
as well as other data such as "Retailer", "Store" and "Date".
Everything works fine, BUT...

I want to calculate the average number of SKU's for a given "Brand" by
"Retailer"

For example, if I use 'Sum of SKU' I get an amount of 15 for "Rods City
Store" and 10 for "Rods East Mall" ("Rods" being the Retailer and "Rods City
Store" being a Store). That's fine.
But if I use 'Average of SKU' I get numbers 1.667 and 2 respectively.

What I want is an average for "Rods" of 12.5.

I could use a calculated field, but the nature of the pivot table means that
the number of columns changes, so I'm not sure how to write the formula.

Also, it would help if anyone can explai where those low averages are coming
from. Is it perhaps an average per character even though that is not the
criteria used in the pivot?

Thanks,

Graeme