If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 PivotTable>Formulas>Calculated 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 PivotTable>Formulas>Calculated 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 PivotTable>Formulas>Calculated 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 > > > > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Table Lookup formula where 2 known values are inside array  excellookuper  New Users to Excel  3  May 25th 07 05:49 AM 
Change value inside a table, vlookup doesn't recalc automatically  XLNuckleHed  Excel Discussion (Misc queries)  1  October 27th 06 06:28 PM 
How do I link a cell outside a pivot table to one inside the table  GPGTDRVR  Excel Discussion (Misc queries)  3  August 17th 06 02:45 AM 
CAlculated Fiel inside a pivot table  leslie  Excel Discussion (Misc queries)  0  June 23rd 06 06:24 PM 
Is it possible to create a table inside of a cell?  Jose  Excel Discussion (Misc queries)  1  January 21st 05 07:47 AM 