A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Formula inside a Pivot Table



 
 
Thread Tools Display Modes
  #1  
Old November 28th 08, 04:08 PM posted to microsoft.public.excel.misc
Mwillie15
external usenet poster
 
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
Ads
  #2  
Old November 28th 08, 04:42 PM posted to microsoft.public.excel.misc
Eduardo
external usenet poster
 
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  
Old November 28th 08, 04:51 PM posted to microsoft.public.excel.misc
Mwillie15
external usenet poster
 
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  
Old November 28th 08, 06:12 PM posted to microsoft.public.excel.misc
Eduardo
external usenet poster
 
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  
Old November 29th 08, 02:18 AM posted to microsoft.public.excel.misc
Debra Dalgleish
external usenet poster
 
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 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  
Old December 16th 08, 04:51 PM posted to microsoft.public.excel.misc
guat
external usenet poster
 
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 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  
Old December 16th 08, 05:14 PM posted to microsoft.public.excel.misc
guat
external usenet poster
 
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 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 06:47 AM


All times are GMT +1. The time now is 04:41 AM.


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