Excel 2007 - Pivottable formula with COUNT
 

I have created a pivot table with total dollar sales for several stores. I
have many columns, but one does show me COUNT on number of stores (e.g. 202)
and one shows dollar sales for the stores it covers (e.g. 1,567,580). I now
want to create a formula that takes the dollar sales and divides by store
count to provide dollars per store (1,567580/202 = $7,758.32/store)

I can do it mannually by adding a formula in cell after the table, but as I
expand and collapse the table, I may or may not have enough cells with
formula to cover each one.

I tried to create a formula within the pivot table ('Dollar Sales'
/COUNT('Dollar Sales')) but it seems to ignore the COUNT and provides the
exact same number just for 'Dollar Sales'.

Any suggestions? I am working in Excel 2007, so a bit different as to what
Pivottable does or does not do.

Thanks for all the help!
--
Peter

Excel 2007 - Pivottable formula with COUNT
 

A calculated field will use the sum of the underlying values, even if a
different summary function is used in the pivot table layout.

You could add a field to the data source, e.g. StoreCount, and enter a 1
in each row. Use that field in the calculation, instead of the count of
stores.

peter wrote:
I have created a pivot table with total dollar sales for several stores. I
have many columns, but one does show me COUNT on number of stores (e.g. 202)
and one shows dollar sales for the stores it covers (e.g. 1,567,580). I now
want to create a formula that takes the dollar sales and divides by store
count to provide dollars per store (1,567580/202 = $7,758.32/store)

I can do it mannually by adding a formula in cell after the table, but as I
expand and collapse the table, I may or may not have enough cells with
formula to cover each one.

I tried to create a formula within the pivot table ('Dollar Sales'
/COUNT('Dollar Sales')) but it seems to ignore the COUNT and provides the
exact same number just for 'Dollar Sales'.

Any suggestions? I am working in Excel 2007, so a bit different as to what
Pivottable does or does not do.

Thanks for all the help!


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

Excel 2007 - Pivottable formula with COUNT
 

Thanks, this does what I need.

Would be nice if user could tell it when to do count.
--
Peter


"Debra Dalgleish" wrote:

A calculated field will use the sum of the underlying values, even if a
different summary function is used in the pivot table layout.

You could add a field to the data source, e.g. StoreCount, and enter a 1
in each row. Use that field in the calculation, instead of the count of
stores.

peter wrote:
I have created a pivot table with total dollar sales for several stores. I
have many columns, but one does show me COUNT on number of stores (e.g. 202)
and one shows dollar sales for the stores it covers (e.g. 1,567,580). I now
want to create a formula that takes the dollar sales and divides by store
count to provide dollars per store (1,567580/202 = $7,758.32/store)

I can do it mannually by adding a formula in cell after the table, but as I
expand and collapse the table, I may or may not have enough cells with
formula to cover each one.

I tried to create a formula within the pivot table ('Dollar Sales'
/COUNT('Dollar Sales')) but it seems to ignore the COUNT and provides the
exact same number just for 'Dollar Sales'.

Any suggestions? I am working in Excel 2007, so a bit different as to what
Pivottable does or does not do.

Thanks for all the help!


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