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.

 Formula inside a Pivot Table
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Formula inside a Pivot Table

#1
November 28th 08, 04:08 PM posted to microsoft.public.excel.misc
 Mwillie15 external usenet poster Posts: 3
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
November 28th 08, 04:42 PM posted to microsoft.public.excel.misc
 Eduardo external usenet poster Posts: 2,276
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
November 28th 08, 04:51 PM posted to microsoft.public.excel.misc
 Mwillie15 external usenet poster Posts: 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
November 28th 08, 06:12 PM posted to microsoft.public.excel.misc
 Eduardo external usenet poster Posts: 2,276
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
November 29th 08, 02:18 AM posted to microsoft.public.excel.misc
 Debra Dalgleish external usenet poster Posts: 2,979
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
December 16th 08, 04:51 PM posted to microsoft.public.excel.misc
 guat external usenet poster Posts: 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
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
December 16th 08, 05:14 PM posted to microsoft.public.excel.misc
 guat external usenet poster Posts: 6
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 Linear Mode

 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 User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 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 10:20 AM.