Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 349
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,979
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 349
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 PivotTable Question Dale Howard [MVP] Excel Worksheet Functions 3 August 28th 07 12:48 PM
Can I create PivotTable formula using count of field not sum? Clip Excel Discussion (Misc queries) 0 November 7th 06 02:31 AM
Excel 2007 PivotTable and Chart Michael Excel Discussion (Misc queries) 0 June 27th 06 09:31 PM
Excel 2007 - The connection for this PivotTable has been deleted Michael Excel Discussion (Misc queries) 1 June 22nd 06 08:54 PM
Why does my Excel pivottable uses count instead of sum Marcus Ahlbäck Excel Discussion (Misc queries) 3 November 8th 05 04:15 PM


All times are GMT +1. The time now is 06:29 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"