Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percent (field1/ field2) on pivot table
Hi, I have the following pivotal table
year region1 region 2 2001 customer_cnt sales_total customer_cnt sales_total 2002 customer_cnt sales_total customer_cnt sales_total 2003 customer_cnt sales_total customer_cnt sales_total ... 2006 customer_cnt sales_total customer_cnt sales_total I would like to create the sales per customer field (sales_total/customer_cnt) for each year and region, but do not know how to do it. Your kind answers will be greatly appreciated. Jay p.s. I believe this question may have been answered in the past. But, I could not find the solutions easily on line.... Sorry. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percent (field1/ field2) on pivot table
Can you go into formulas on the pivot chart toolbar, then enter the formula
in there (double clicking on the fields you want). You need to use the toolbar, just right clicking doesn't give you the formula option. Regards, Light "David Chang" wrote: Hi, I have the following pivotal table year region1 region 2 2001 customer_cnt sales_total customer_cnt sales_total 2002 customer_cnt sales_total customer_cnt sales_total 2003 customer_cnt sales_total customer_cnt sales_total ... 2006 customer_cnt sales_total customer_cnt sales_total I would like to create the sales per customer field (sales_total/customer_cnt) for each year and region, but do not know how to do it. Your kind answers will be greatly appreciated. Jay p.s. I believe this question may have been answered in the past. But, I could not find the solutions easily on line.... Sorry. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percent (field1/ field2) on pivot table
Here are some more details.
Assume your base data had these headers and ranges: Year (2000-2005) Reg (Reg1, Reg2) Type (CCount,STotal) Value (any number) Then your Pivot Table might looks like this: Sum of Value Reg Type Reg1 Reg2 Year CCount STotal ST/CC CCount STotal ST/CC 2000 72 526 7.31 132 904 6.85 2001 84 1285 15.30 49 215 4.39 2002 194 3466 17.87 76 758 9.97 2003 241 3357 13.93 31 988 31.87 2004 46 1458 31.70 67 386 5.76 2005 350 1786 5.10 180 660 3.67 ST/CC are the added fields, so ignore them for the moment. Click on the cell that says CCount or STotal (not Reg1 or Reg2). From the Pivot Table tool bar, select Formula Calculated Item (not Calculated Field) In the Fields box on the left, select Type. From the Items box on the right, select STotal and CCount so the formula will read: =STotal/CCount Change the name from Formula1 to ST/CC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Filename reference in Spreadsheet XML issue | Excel Discussion (Misc queries) | |||
Display the source for a pivot table page field | Excel Worksheet Functions | |||
Insert a row after a pivot table | Excel Discussion (Misc queries) | |||
Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
Pivot table question: How to display total and percent for data simultaneouly | Excel Discussion (Misc queries) |