Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 PivotTable Question | Excel Worksheet Functions | |||
Can I create PivotTable formula using count of field not sum? | Excel Discussion (Misc queries) | |||
Excel 2007 PivotTable and Chart | Excel Discussion (Misc queries) | |||
Excel 2007 - The connection for this PivotTable has been deleted | Excel Discussion (Misc queries) | |||
Why does my Excel pivottable uses count instead of sum | Excel Discussion (Misc queries) |