Presenting Pivot table data as product of 2 source data columns

I have the following data

F1	F2	D1	D2
F01	1	10	1
F02	2	20	3
F01	2	30	6
F01	5	40	4
F02	6	40	5

F1 to be presented in rows
F2 to be presented in columns
D1* D2 to be presented as data

Is that possible without defining an extra column in my source data?

regards, Louis
0
icefagur (2)
11/10/2009 12:10:19 PM
excel 39879 articles. 2 followers. Follow

4 Replies
1278 Views

Similar Articles

[PageSpeed] 46

Louise - Yes, it's a calculated pivottable field. Once you have the 
pivottable established except for the data values, select Options tab, 
Formulas drop down arrow, calculated field.

"loconel" wrote:

> 
> I have the following data
> 
> F1	F2	D1	D2
> F01	1	10	1
> F02	2	20	3
> F01	2	30	6
> F01	5	40	4
> F02	6	40	5
> 
> F1 to be presented in rows
> F2 to be presented in columns
> D1* D2 to be presented as data
> 
> Is that possible without defining an extra column in my source data?
> 
> regards, Louis
> .
> 
0
BobRyan (7)
11/12/2009 1:57:01 AM
On Nov 11, 6:57=A0pm, Bob Ryan <BobR...@discussions.microsoft.com>
wrote:
> Louise - Yes, it's a calculated pivottable field. Once you have the
> pivottable established except for the data values, select Options tab,
> Formulas drop down arrow, calculated field.

Is this by chance an Excel 2007 feature?  I'm running Excel 2003, and
I cannot find an Options "tab".

Kevin

> "loconel" wrote:
>
> > I have the following data
>
> > F1 F2 =A0 =A0 =A0D1 =A0 =A0 =A0D2
> > F01 =A0 =A0 =A0 =A01 =A0 =A0 =A0 10 =A0 =A0 =A01
> > F02 =A0 =A0 =A0 =A02 =A0 =A0 =A0 20 =A0 =A0 =A03
> > F01 =A0 =A0 =A0 =A02 =A0 =A0 =A0 30 =A0 =A0 =A06
> > F01 =A0 =A0 =A0 =A05 =A0 =A0 =A0 40 =A0 =A0 =A04
> > F02 =A0 =A0 =A0 =A06 =A0 =A0 =A0 40 =A0 =A0 =A05
>
> > F1 to be presented in rows
> > F2 to be presented in columns
> > D1* D2 to be presented as data
>
> > Is that possible without defining an extra column in my source data?
>
> > regards, Louis
> > .- Hide quoted text -
>
> - Show quoted text -

0
knechod (2)
11/14/2009 12:54:58 AM
Kevin - It's in Excel2003 as well, just in a different place. After you do 
the pivottable, there's a pivottable toolbar. Click the PivotTable dropdown 
arrow, select Formulas, then Calculated Field. If you don't see Formulas, you 
might need to click the double down arrows at the bottom of that dropdown 
selection. Hope this helps. 

"knechod" wrote:

> On Nov 11, 6:57 pm, Bob Ryan <BobR...@discussions.microsoft.com>
> wrote:
> > Louise - Yes, it's a calculated pivottable field. Once you have the
> > pivottable established except for the data values, select Options tab,
> > Formulas drop down arrow, calculated field.
> 
> Is this by chance an Excel 2007 feature?  I'm running Excel 2003, and
> I cannot find an Options "tab".
> 
> Kevin
> 
> > "loconel" wrote:
> >
> > > I have the following data
> >
> > > F1 F2      D1      D2
> > > F01        1       10      1
> > > F02        2       20      3
> > > F01        2       30      6
> > > F01        5       40      4
> > > F02        6       40      5
> >
> > > F1 to be presented in rows
> > > F2 to be presented in columns
> > > D1* D2 to be presented as data
> >
> > > Is that possible without defining an extra column in my source data?
> >
> > > regards, Louis
> > > .- Hide quoted text -
> >
> > - Show quoted text -
> 
> .
> 
0
BobRyan (7)
11/14/2009 6:07:01 PM
On Nov 14, 11:07=A0am, Bob Ryan <BobR...@discussions.microsoft.com>
wrote:
> Kevin - It's in Excel2003 as well, just in a different place. After you d=
o
> the pivottable, there's a pivottable toolbar. Click the PivotTable dropdo=
wn
> arrow, select Formulas, then Calculated Field. If you don't see Formulas,=
 you
> might need to click the double down arrows at the bottom of that dropdown
> selection. Hope this helps.

It does indeed!  I've been looking ALL OVER for a way to express Sum
(Field1) / Sum(Field2),
and I was getting nowhere.

Thanks much!

> "knechod" wrote:
> > On Nov 11, 6:57 pm, Bob Ryan <BobR...@discussions.microsoft.com>
> > wrote:
> > > Louise - Yes, it's a calculated pivottable field. Once you have the
> > > pivottable established except for the data values, select Options tab=
,
> > > Formulas drop down arrow, calculated field.
>
> > Is this by chance an Excel 2007 feature? =A0I'm running Excel 2003, and
> > I cannot find an Options "tab".
0
knechod (2)
11/16/2009 6:02:28 PM
Reply:

Similar Artilces:

Splitting contents from multiple repeating cells in 1st column into 4 columns
In my first column I have 4 cells (1-4) (5-8) etc. with general content. The content from each of these 4 cells needs to be placed in a separate column to make a list that I can sort etc. Example: COLUMN A 1 01-04-425-001 2 Heatherridge Road #301 3 Harry Smith 4 H25 Condo Fairfax Place 5 01-05-356-041 6 McGrath Street # 56 7 Mary Jones 8 B45 Condo Lemon Circle . . . . . . and so on and so on every 4 cells. I have hundreds of 4 cell descriptions. I just can't do this one by one. Can anyone help, PLEASE, PLEASE. I am really a novice at Excel though I use it for everything ...

How to lock table on the left?
Hi All, I saw a excel file, one of its table (one row) could keep in the left sied of screen when I scrolling the scrolling bar. How to do that ? Thanks a lot! Best regards, Boki. Tinker with this .. Select B2 Click Window > Freeze pane Now you can scroll right, and col A will always be in view (frozen) or scroll down, and row1 will always be in view (frozen) To undo, just click Window > UnFreeze Panes -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "boki" <bokiteam@ms21.hinet.net> wrote in message new...

Sorting Grouped Data?
Hi, I have a sales spreadsheet that is grouped by vendor with customer sales info under each vendor. Is there a way to sort the vendor groups keeping the customer data in each groups as is? TIA, Dan This may work Dan. To sort in Excel all the lines need to have same columns to sort on. So a heading line and a space between Vendor Groups would be be a problem You could add 2 columns (In the Example below columns A and B) with sort data criteria so that the Heading and Space positions could be preserved. Column A defines the Vendor Group Column B defines the sort priority Then Sort b...

Earn money easily! PayPal!, Don'T miss this! #2
<HTML> <HEAD> <META NAME="GENERATOR" Content="Microsoft DHTML Editing Control"> <TITLE></TITLE> </HEAD> <BODY> <P>&nbsp;</P> </BODY> </HTML> ...

Pivot Table in a Shared Workbook
Hi! Can I use pivottables in a shared workbook? The reason I ask is that its greyed out in a shared WB but not in a non-shared. I can't find anything on this, perhaps somebody can help? thanks! Chris You can't change or create a pivot table in a shared workbook. For more information on shared workbooks, look in Excel's Help, under the topic 'Features that are unavailable in shared workbooks' Riskybizniz wrote: > Hi! Can I use pivottables in a shared workbook? The reason I ask is that its > greyed out in a shared WB but not in a non-shared. I can't find any...

backup same set of servers from 2 DPM 2007 servers
I am changing the DPM server 2007. However, I would like to take this opportunity to revise all protection groups so that I can restore bare-metal any server from a single lTO-4 tape if possible. During this "transitional period," I would like to leave the existing backup settings on the older DPM server to continue to run while adding jobs to the new DPM server. Looks like to me that I have to re-install the agent on all servers so that they can be recognized by the new DPM server. IS there a better way to do this if my plan is possible at all? Thanks Bill ...

Registration of Office products
I have installed a new server and 1 new computer in my office for a non profit organisation. With my new computer came an OEM copy of MS OFFICE PROFESSIONAL EDITION 2003 and I would like to install it on 3 other office computers that are currently running OFFICE 2000 and a personal computer. Is this possible No. An OEM copy is licensed only for your use on the machine with which it was purchased. You will need to buy copies for the other machines. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers ...

Presentation Properties
I have PPT 2007. I have always wondered how to make good use of the Powerpoint Properties dialog box. How can Keywords and Category, for example, be put to good use? What searching system can look inside the properties? Are any properties useful for SEO if a presentation is posted on a website? Thank you. Alessandra In article <51fec317-0258-4865-8da6- dff4d72bdfae@10g2000yqq.googlegroups.com>, Alessandra wrote: > I have PPT 2007. I have always wondered how to make good use of the > Powerpoint Properties dialog box. > How can Keywords and Category, for example, be...

Import External Data
Hi all, I'm trying to use the "Import External Data" function in Excel 2003 to import data from one spreadsheet to another. It works for some fields (specifically ,date and text fields), but for others no data is imported (specifically, whole numbers, percentage fields and dollar fields). Is there some way to import the missing data? Thank you, Art. importing external data is usually used to import data form a REAL data store-- like an Access database or a SQL Server database. Basically; Microsoft is too flaky to make these simple things work correctly. because t...

rearrangimg data in excel2007 for merging into word 2007
Dear sir/madam, I quite regularly have to create a list of numbered labels which are printed on a 10 label sheet in word 2007.the list of numbers is created in excell and then merged into the mailmerge area of word Is there a formula that I can use in excel where I create the list that will re order the list from 1,2,3,4… to 1,11,21,31….dependant on the total number of labels needed From the first “set of labels” to the “second set” assuming I wanted 30 labels printed the formulae would have to take into account the total number of labels needed. The lising I create is like this ...

First Pivot Table
Hi Everyone, Using Excel 2003. I created my first pivot table being guided over the phone and didn't quite remember how I got it grouped by years and quarters when I tried to do it on my own. I do have a screenshot of the pivot Table Wizard -layout and in my Page box I have Therapy Description Row section I have Quarters and Therapy End Date Data section I have Count of Admit Number and Average of Vent LOS I can reproduce everything but the Quarters because I don't have that label in my new wizard to drag over to my Row section. I have right clicked and double cli...

RE: Installation of CRM Outlook client v1.2
Hello happy hunters, BACKGROUND INFO Having serious issues installing the Outlook CRM client v1.2 on a Windows 2000 / Office 2003 (Certified Partner copy). Have the Server v1.2 installed on a Windows 2000 Server with SQL Server 2000 SP3 and also have SQL server existing on the client machine but have 'had' the Outlook client v1.0 working in this config (but this was with Office 2000 SP3 + hotfix). I guess I made the mistake of uninstalling the CRM client (including MSDE) before uninstalling Office 2000, and then installing Office 2003 followed by attempting to install the ...

Outlook running very slow #2
Runnng Outlook 2000 and I have about 2,000 contacts. Outlook moves slow in my opinion and contacts do not open up immediately as they should. Are there any suggestions? Can I reindex data in any way or does anyone have an opinion on number of contacts. ...

Pivot Table Help #3
Good Morning I have a pivot table set up and all is ok i have set up a macro to copy the data in the pivot table and to paste it sumwhere else and to sort it all is good, but when i save the spreadsheet and close it down and reopen it and run the macro the pivot table shrinks down to 2 rows and 2 colums how do i get my data back on show Can anyone help Thanks in Advance Steve ...

Pivot Table Change to US Date Format
Hi, I have a set of pivot tables which are being updated by cell the date in cell B1 using the following code: ActiveSheet.PivotTables("PivotTable1").PivotFields("Invoice Month").CurrentPage = ActiveSheet.Range("B1").Value At last this is working :) However... the dates are being inserted into the pivot tables as new selections in US date format instead of selecting an existing UK date format from the data choices. I do not understand this as all source data is in UK date format, cell B1 is in UK date format, the pivot field is also in UK da...

Graphs not present
I cannot seem to be able to view graphs in Excel 2002. Any ideas.... DMJ Which graphs would those be? Graphs you created? Graphs in a workbook that was sent to you? More detail please. Gord Dibben Excel MVP On Mon, 12 Jan 2004 11:24:32 -0400, "DMJ" <kmd@hotmail> wrote: >I cannot seem to be able to view graphs in Excel 2002. > >Any ideas.... > ...

Importing a PST File #2
Further to my previous Posts. 1. I have backed up the outlook.pst file to CD using NERO 2. I have restored the outlook.pst file to a seperate folder 3. I have checked the file attribute and made sure the archive bit is off 4. I have run the scanpst program over the outlook.pst file. SCANPST came up with a large number of errors (I have the file). An example is: !!PMap page @6112768: PTYPE mismatch (read 00, expected 83) !!PMap page @6112768: BID mismatch (read 0, expected 5D4600) When I open or import the outlook.pst file, there are 2 empty folders... recovered and deleted. Any s...

Pivot Chart #9
I want to maintain my selected color for the pivot chart. But, when i filter the chart, the selected color will refresh back to default color. Any idea to keep my selected color for the pivot chart even i refresh the chart? Thanks. Loss of formatting is a known problem with pivot charts. There's information in the following MSKB article, suggests recording a macro as you apply the formatting: Changing a PivotChart Removes Series Formatting in Excel http://support.microsoft.com/?id=215904 Jojomay wrote: > I want to maintain my selected color for the pivot chart. But, when i...

calendar views #2
hello. i'm running outlook 2003 on xp and have a question about the calendar function. from the navigation pane, selecting CALENDAR opens up the calendar view. on the left pane it displays three months of the year stacked atop each other. the problem is this: the view is really stretched out and far-spaced. on my normal monitor running at 102x768 the view is fine but on my widescreen notebook running at 1440x900, the view is stretched. grabbing the edges to narrow the left pane does nothing but hide the calendar; it doesn't shrink the spacing and constrain it. has anyone...

newbie: How can I get the querystring from the presenter?
Hi asp.net 4.0 vs2010 I wonder how I can get the querystring from within the presenter? Jeff wrote: > Hi > > asp.net 4.0 > vs2010 > > I wonder how I can get the querystring from within the presenter? > > What presenter? Sorry, I thought there was only one type of presenter in the .net framework "Mr. Arnold" <Arnold@Arnold.com> wrote in message news:%23P0csJGHLHA.1972@TK2MSFTNGP04.phx.gbl... > Jeff wrote: >> Hi >> >> asp.net 4.0 >> vs2010 >> >> I wonder how I can get the ...

Export Exchange 5.5 data
I'm somewhat familiar with the format of the Comma Seperated Value (csv) file for the Exchange 5.5 directory import/export procedures. Is there a setting to allow me to capture the last login information for a mailbox? You can use the header.exe file to determine the names of the available attributes to export and create an export file for you. For your specific task, you can always use the Exchange Administrator application to display the last logon time and then choose File / Save Window Contents to create a suitable file. -- Neil Hobson Exchange MVP For Exchange news, links, ...

Arrays and names #2
Hi all. Using XL2002 but needs to be XL97 compatible. With ActiveCell Transactions!F6 I have defined a local named range Transactions!Test refers to =(Assets!Category_Range=Transactions!$E6) Assets!Category_Range is a name that refers to an absolutely defined single column of data The relative reference to row 6 in the above is intentional but probably not relevant for what follows. Evaluating the array Transactions!Test produces something like this: False; False; True; False; True; True; False; <etc> This is correct and predicted behaviour. I want to create another local named range...

Pivot Table Percentage of Grand Total Column
Here is my problem. I am using Pivot Table to summarize financial data to preform a margin analysis on it. Currently I receive a dump of data from the system which turn is put into the pivot table. The way the pivot table is currently displayed is as follows: Sum of Amount Account Customer 40000 43000 50000 Grand Total ABC $4,000.00 $(50.00) $(3,000.00) $950.00 PDQ $6,000.00 $(350.00) $(4,000.00) $1,650.00 XYZ $5,000.00 $(500.00) $(3,500.00) $1,000.00 Grand Total $15,000.00 $(900.00) $(1...

Report on a refreshed table coming up blank
I build a summary table based on many other tables using VBA. Here are the steps: - Clear the table - Insert records from source tables with calculated fields defaulted to 0 - Fill in calcuated field in the table - Print Preview the report based on the table. Problem: The report prints with the calculated fields still at 0. I look at the table and fields are not 0. If I close the report and open it again prints the correct data. It almost seems like a timing issue. Help Please it is dirving me insane!! You must have a complex or unusual application to need to create a table from which to...

RMS 2.0 & Quickbooks pro 2010 Mapping
I am tring to update quick books with sales infromation from RMS to get a more accurat P&L from quickbooks with all exspences not just gross profit how can I map daily sales to go to Quick books from RMS I Pleas drop an email to discuss. MV nj.tech@hotmail.com "AL" wrote: > I am tring to update quick books with sales infromation from RMS to get a > more accurat P&L from quickbooks with all exspences not just gross profit how > can I map daily sales to go to Quick books from RMS I my email is alqzo@msn.com "manick" wrote: >...