Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 sum-product? 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 sum-product? 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) |