Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 28th 08, 05:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3
Default 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   Report Post  
Old November 28th 08, 05:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,276
Default 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   Report Post  
Old November 28th 08, 05:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3
Default 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   Report Post  
Old November 28th 08, 07:12 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 2,276
Default 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   Report Post  
Old November 29th 08, 03:18 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,979
Default 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   Report Post  
Old December 16th 08, 05:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 6
Default 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   Report Post  
Old December 16th 08, 06:14 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2008
Posts: 6
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Table Lookup formula where 2 known values are inside array excel-lookuper 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


All times are GMT +1. The time now is 07:30 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017