Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding columns in pivot tables
Hi,
I have a grouped pivot table that looks like this: Group1 Group2 Item 2007 2008-Q2 2007 2008-Q2 A $50 $30 $100 $55 B $25 $10 $150 $60 C $115 $65 $100 $75 For each group, I am trying to create 2 calculated columns: 2008-Q4: multiplies 2008-Q2 items by 2 %difference: 2008-Q4 to 2007 % increase or decrease the data sould look something like this at the end: Group1 Group2 Item 2007 2008-Q2 2008-Q4 %Diff 2007 2008-Q2 A $50 $30 $60 +20% $100 $55 B $25 $10 $20 -20% $150 $60 C $115 $65 $130 +13% $100 $75 I tried to use calculated fields and it doesn't seem to work for me. Anyone can help Thanks, Karim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding columns in pivot tables
go to the cell you want to put the formula in
enter = then click on the cell you want to multiply then enter *2, copy down repeat the same for the second field basically you have to use GETPIVOTDATA function to get values from PIVOT table. let me know how it goes. "karim" wrote: Hi, I have a grouped pivot table that looks like this: Group1 Group2 Item 2007 2008-Q2 2007 2008-Q2 A $50 $30 $100 $55 B $25 $10 $150 $60 C $115 $65 $100 $75 For each group, I am trying to create 2 calculated columns: 2008-Q4: multiplies 2008-Q2 items by 2 %difference: 2008-Q4 to 2007 % increase or decrease the data sould look something like this at the end: Group1 Group2 Item 2007 2008-Q2 2008-Q4 %Diff 2007 2008-Q2 A $50 $30 $60 +20% $100 $55 B $25 $10 $20 -20% $150 $60 C $115 $65 $130 +13% $100 $75 I tried to use calculated fields and it doesn't seem to work for me. Anyone can help Thanks, Karim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding columns in pivot tables
Hi,
Thanks for the reply. This works, but it wasn't what I wanted. I wanted to embed the new calculated fields in the pivot table. "Sheeloo" wrote: go to the cell you want to put the formula in enter = then click on the cell you want to multiply then enter *2, copy down repeat the same for the second field basically you have to use GETPIVOTDATA function to get values from PIVOT table. let me know how it goes. "karim" wrote: Hi, I have a grouped pivot table that looks like this: Group1 Group2 Item 2007 2008-Q2 2007 2008-Q2 A $50 $30 $100 $55 B $25 $10 $150 $60 C $115 $65 $100 $75 For each group, I am trying to create 2 calculated columns: 2008-Q4: multiplies 2008-Q2 items by 2 %difference: 2008-Q4 to 2007 % increase or decrease the data sould look something like this at the end: Group1 Group2 Item 2007 2008-Q2 2008-Q4 %Diff 2007 2008-Q2 A $50 $30 $60 +20% $100 $55 B $25 $10 $20 -20% $150 $60 C $115 $65 $130 +13% $100 $75 I tried to use calculated fields and it doesn't seem to work for me. Anyone can help Thanks, Karim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding columns in pivot tables
Instead of a calculated field, use a calculated item:
Select one of the heading cells for the dates. On the pivot table toolbar, click PivotTableFormulasCalculated Item Type a name for the calculated item, e.g. 2008-Q4 Enter the formula, and click Add Enter a name and formula for the second calculated item. Click OK, to close the dialog box and see the results in the pivot table. karim wrote: Hi, I have a grouped pivot table that looks like this: Group1 Group2 Item 2007 2008-Q2 2007 2008-Q2 A $50 $30 $100 $55 B $25 $10 $150 $60 C $115 $65 $100 $75 For each group, I am trying to create 2 calculated columns: 2008-Q4: multiplies 2008-Q2 items by 2 %difference: 2008-Q4 to 2007 % increase or decrease the data sould look something like this at the end: Group1 Group2 Item 2007 2008-Q2 2008-Q4 %Diff 2007 2008-Q2 A $50 $30 $60 +20% $100 $55 B $25 $10 $20 -20% $150 $60 C $115 $65 $130 +13% $100 $75 I tried to use calculated fields and it doesn't seem to work for me. Anyone can help Thanks, Karim -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adding columns in pivot tables
This doesn't work either. I get an error message "there are too many records
to complete this operation" (my data is 15,000 rows) I am not sure what is ther right use for calculated field and calculated item. is this explained anywhere online? You don't have this explained in your tutorial on your web site (contextures) Thanks, Karim "Debra Dalgleish" wrote: Instead of a calculated field, use a calculated item: Select one of the heading cells for the dates. On the pivot table toolbar, click PivotTableFormulasCalculated Item Type a name for the calculated item, e.g. 2008-Q4 Enter the formula, and click Add Enter a name and formula for the second calculated item. Click OK, to close the dialog box and see the results in the pivot table. karim wrote: Hi, I have a grouped pivot table that looks like this: Group1 Group2 Item 2007 2008-Q2 2007 2008-Q2 A $50 $30 $100 $55 B $25 $10 $150 $60 C $115 $65 $100 $75 For each group, I am trying to create 2 calculated columns: 2008-Q4: multiplies 2008-Q2 items by 2 %difference: 2008-Q4 to 2007 % increase or decrease the data sould look something like this at the end: Group1 Group2 Item 2007 2008-Q2 2008-Q4 %Diff 2007 2008-Q2 A $50 $30 $60 +20% $100 $55 B $25 $10 $20 -20% $150 $60 C $115 $65 $130 +13% $100 $75 I tried to use calculated fields and it doesn't seem to work for me. Anyone can help Thanks, Karim -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pivot tables - add columns | Excel Worksheet Functions | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
Pivot Tables - Adding Absolute Sum | Excel Discussion (Misc queries) | |||
Pivot Tables - # of Columns | Excel Discussion (Misc queries) | |||
Pivot tables - inserting columns | Excel Worksheet Functions |