Home 
Search 
Today's Posts 
#1




Formula inside a Pivot Table
Inside my raw data I have a series of sku's with qty sold (units) and sale
date (2007 and 2008) as follows: SKU A 2 2007 SKU B 3 2007 SKU A 4 2008 SKU B 2 2008 When I create a pivot table, I have the sku as the row header and the two years as the column headers. How can I calculate a thrid column that would represent the difference? In the calculate field menu, I cannot figure out how to create a formula that will calculate a result data that origninally is in the same column. Any suggestions? mwillie15 
#2




Formula inside a Pivot Table
Hi,
Why you don't have 2007 & 2008 in two different columns, in that way you will be able to have a 3rd column with the difference "Mwillie15" wrote: Inside my raw data I have a series of sku's with qty sold (units) and sale date (2007 and 2008) as follows: SKU A 2 2007 SKU B 3 2007 SKU A 4 2008 SKU B 2 2008 When I create a pivot table, I have the sku as the row header and the two years as the column headers. How can I calculate a thrid column that would represent the difference? In the calculate field menu, I cannot figure out how to create a formula that will calculate a result data that origninally is in the same column. Any suggestions? mwillie15 
#3




Formula inside a Pivot Table
Eduardo,
Thanks for the answer, unfortunately, the data is from a database dump with multiple columns. I figured this might be the case but I wanted to avoid manipulating the data and do it via a pivot table. Thanks for the help. Anyone else? mwillie15 "Eduardo" wrote: Hi, Why you don't have 2007 & 2008 in two different columns, in that way you will be able to have a 3rd column with the difference "Mwillie15" wrote: Inside my raw data I have a series of sku's with qty sold (units) and sale date (2007 and 2008) as follows: SKU A 2 2007 SKU B 3 2007 SKU A 4 2008 SKU B 2 2008 When I create a pivot table, I have the sku as the row header and the two years as the column headers. How can I calculate a thrid column that would represent the difference? In the calculate field menu, I cannot figure out how to create a formula that will calculate a result data that origninally is in the same column. Any suggestions? mwillie15 
#4




Formula inside a Pivot Table
Hi,
In that case you can create your data based on the database let's in column k you have your sku #, in column l 2007 and in Column m 2008 then enter the formula as follow and it will bring all the information needed =SUMPRODUCT(($G$4:$G$7=$K4),($I$4:$I$7=L$3),$H$4:$H$7) "Mwillie15" wrote: Eduardo, Thanks for the answer, unfortunately, the data is from a database dump with multiple columns. I figured this might be the case but I wanted to avoid manipulating the data and do it via a pivot table. Thanks for the help. Anyone else? mwillie15 "Eduardo" wrote: Hi, Why you don't have 2007 & 2008 in two different columns, in that way you will be able to have a 3rd column with the difference "Mwillie15" wrote: Inside my raw data I have a series of sku's with qty sold (units) and sale date (2007 and 2008) as follows: SKU A 2 2007 SKU B 3 2007 SKU A 4 2008 SKU B 2 2008 When I create a pivot table, I have the sku as the row header and the two years as the column headers. How can I calculate a thrid column that would represent the difference? In the calculate field menu, I cannot figure out how to create a formula that will calculate a result data that origninally is in the same column. Any suggestions? mwillie15 
#5




Formula inside a Pivot Table
Instead of a calculated field, you can create a calculated item:
Click on one of the Year headings On the pivot table toolbar, click PivotTableFormulasCalculated Item Type a name, e.g. Diff For the formula, enter: = '2008' '2007' Click OK Mwillie15 wrote: Inside my raw data I have a series of sku's with qty sold (units) and sale date (2007 and 2008) as follows: SKU A 2 2007 SKU B 3 2007 SKU A 4 2008 SKU B 2 2008 When I create a pivot table, I have the sku as the row header and the two years as the column headers. How can I calculate a thrid column that would represent the difference? In the calculate field menu, I cannot figure out how to create a formula that will calculate a result data that origninally is in the same column. Any suggestions? mwillie15  Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com 
#6




Formula inside a Pivot Table
Hi! I'm trying to do the same thing in a different way:
I have a pivot table with row headers for Active Terminated Dept 1 Dept 2 Dept 3 etc.. I would like to create a 3rd column called turnover that divides terminated/active. I try your suggestion below that you gave to mwillie, but the Calculated Item is grayed out. I can use calculated field, but can't get it to work. Is there a way to do this within the pivot table and not using a sumproduct? Thanks. Varvara "Debra Dalgleish" wrote: Instead of a calculated field, you can create a calculated item: Click on one of the Year headings On the pivot table toolbar, click PivotTableFormulasCalculated Item Type a name, e.g. Diff For the formula, enter: = '2008' '2007' Click OK Mwillie15 wrote: Inside my raw data I have a series of sku's with qty sold (units) and sale date (2007 and 2008) as follows: SKU A 2 2007 SKU B 3 2007 SKU A 4 2008 SKU B 2 2008 When I create a pivot table, I have the sku as the row header and the two years as the column headers. How can I calculate a thrid column that would represent the difference? In the calculate field menu, I cannot figure out how to create a formula that will calculate a result data that origninally is in the same column. Any suggestions? mwillie15  Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com 
#7




Formula inside a Pivot Table
Hi Debra, are you there? I figured out why it was grey, I wasn't actually in
the header row. So now I got it to work by what you said, and did a percentage. Now my problem is at the grand total at the bottom, it is summing up all of the percentages rather than calculating a total percentage. Anyway you can help me with this? Thanks, Varvara "guat" wrote: Hi! I'm trying to do the same thing in a different way: I have a pivot table with row headers for Active Terminated Dept 1 Dept 2 Dept 3 etc.. I would like to create a 3rd column called turnover that divides terminated/active. I try your suggestion below that you gave to mwillie, but the Calculated Item is grayed out. I can use calculated field, but can't get it to work. Is there a way to do this within the pivot table and not using a sumproduct? Thanks. Varvara "Debra Dalgleish" wrote: Instead of a calculated field, you can create a calculated item: Click on one of the Year headings On the pivot table toolbar, click PivotTableFormulasCalculated Item Type a name, e.g. Diff For the formula, enter: = '2008' '2007' Click OK Mwillie15 wrote: Inside my raw data I have a series of sku's with qty sold (units) and sale date (2007 and 2008) as follows: SKU A 2 2007 SKU B 3 2007 SKU A 4 2008 SKU B 2 2008 When I create a pivot table, I have the sku as the row header and the two years as the column headers. How can I calculate a thrid column that would represent the difference? In the calculate field menu, I cannot figure out how to create a formula that will calculate a result data that origninally is in the same column. Any suggestions? mwillie15  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  
Table Lookup formula where 2 known values are inside array  New Users to Excel  
Change value inside a table, vlookup doesn't recalc automatically  Excel Discussion (Misc queries)  
How do I link a cell outside a pivot table to one inside the table  Excel Discussion (Misc queries)  
CAlculated Fiel inside a pivot table  Excel Discussion (Misc queries)  
Is it possible to create a table inside of a cell?  Excel Discussion (Misc queries) 