Pivot Table and adding a % column, that is not in original data

Hi, Is it possible to add a column for % calculations when the % column is 
not in original data? To clarify, my original data is as follows:

Produt Sales Returns Date
A           5                    June
B           6                    June
A                     1          July
A                     1          September
B                      1         November

When I run the pivot table, one of the columns I'm then looking to get is a 
total % of returns over sales , but I cant see how to include in a Pivot 
table. I can add it outside of the table, but that has problems when the 
table is refreshed etc, as it frequently needs to be.

Thanks for any help
0
Utf
2/23/2010 5:40:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
479 Views

Similar Articles

[PageSpeed] 41

Which EXCEL version?

"MarkArtillery" wrote:

> Hi, Is it possible to add a column for % calculations when the % column is 
> not in original data? To clarify, my original data is as follows:
> 
> Produt Sales Returns Date
> A           5                    June
> B           6                    June
> A                     1          July
> A                     1          September
> B                      1         November
> 
> When I run the pivot table, one of the columns I'm then looking to get is a 
> total % of returns over sales , but I cant see how to include in a Pivot 
> table. I can add it outside of the table, but that has problems when the 
> table is refreshed etc, as it frequently needs to be.
> 
> Thanks for any help
0
Utf
2/23/2010 5:44:04 PM
From the PivotTable toolbar, click the PivotTable button, formulas, 
calculated field. Then setup the formula to be:
=Returns/Sales

-- 
Best Regards,

Luke M
"MarkArtillery" <MarkArtillery@discussions.microsoft.com> wrote in message 
news:A3088499-1779-45D4-84B0-1DAE144C1767@microsoft.com...
> Hi, Is it possible to add a column for % calculations when the % column is
> not in original data? To clarify, my original data is as follows:
>
> Produt Sales Returns Date
> A           5                    June
> B           6                    June
> A                     1          July
> A                     1          September
> B                      1         November
>
> When I run the pivot table, one of the columns I'm then looking to get is 
> a
> total % of returns over sales , but I cant see how to include in a Pivot
> table. I can add it outside of the table, but that has problems when the
> table is refreshed etc, as it frequently needs to be.
>
> Thanks for any help 


0
Luke
2/23/2010 5:50:19 PM
Mark, please refer to:-

http://www.pierrefondes.com/

Items 70 and 71 towards the top of my home page. 

The EXCEL 2007 file is the final version of what I have done (I think that 
it gives you what you want). 

The WORD 2007 file shows you how I got there. 

Please hit Yes if my comments have helped. 

Thanks. 



"MarkArtillery" wrote:

> Hi, Is it possible to add a column for % calculations when the % column is 
> not in original data? To clarify, my original data is as follows:
> 
> Produt Sales Returns Date
> A           5                    June
> B           6                    June
> A                     1          July
> A                     1          September
> B                      1         November
> 
> When I run the pivot table, one of the columns I'm then looking to get is a 
> total % of returns over sales , but I cant see how to include in a Pivot 
> table. I can add it outside of the table, but that has problems when the 
> table is refreshed etc, as it frequently needs to be.
> 
> Thanks for any help
0
Utf
2/23/2010 6:21:01 PM
Reply:

Similar Artilces:

change the text in the legend of a chart w/o changing pivot text
I am trying to create a series of pie charts that compares demographic data for clients assisted with multiple housing-related activities (new homebuyer, rehab, foreclosure prevention, etc.) If I want to look at the age of new homebuyers, on my pivot table I deselect all activities except new homebuyer. After I make my pie chart, in the legend the labels are "new homebuyer 18-24", "new homebuyer 25-34", etc. It's pretty self-explanatory that these are all "new homebuyers". I need for the New Homebuyer part to not be shown in the legend, but I can...

Pivot Table #21
In my pivot table i have days on the market. I need to sort them so that they show in groups such as: 1-100,101-200,201-300,301-400. then a second column showing values number sold such as:1-100 31 then a third column showing the avg. sales price suchas:1-100 31 $94,451.61 Hi Right click on a value>Group and Outline>Group>by 100>OK -- Regards Roger Govier "fearthespear" <fearthespear@discussions.microsoft.com> wrote in message news:67455DF6-C775-4758-B731-65805B359728@microsoft.com... > In my pivot table i have days on the market. I need to sort them s...

Microsoft CRM data
Hi, I'm building the Web Service example in Chapter 3 of the "Microsoft Office Information Bridge (MOIB) Solution Development Guide" (Beta). I'll use the Web Service to create the smart tag recognizer and actions. I've previously built the Web Service example in the "Integrating Microsoft CRM (MSCRM) with Microsoft Office 2003 InfoPath", February 2004. The MSCRM example uses http://<CRM_Server_Name>/mscrmservices as the connection string. The MOIB example uses a direct "CRM" database connection string in the web.config file, and not a CRM serv...

Current time for data entered in column
Whenever data is entered in a cell, current time should come auto i another corresponding cell. For example, time should automaticall come in Y column for any data entered in any cell of the column A. Fo A10 time should come in Y10 and for A12 it should come in Y12 and s on. I need it for doing time and motion study. Appreciate all help -- Message posted from http://www.ExcelForum.com Hi Mohitmahajan! Based on JE McGimpsey (http://www.mcgimpsey.com/excel/timestamp.html) Let's say that every time an entry is made in cells A2:A100, the corresponding cell in column Y should have the...

Deleting Sales Receipts
I need to reduce my db size... Many have posted about the Retail Realms archiving utility. This sounds like a great app, but since I'm somewhat familiar with the db schema in RMS and know SQL, I would like to attempt it on my own. Has anyone tried removing sales history through SQL? What tables should be cleared, does anyone have a script that they use? I understand that a backup should be made prior to any changes :) Paul Paul, To see what tables are taking up all the space run this query: ------------------------- SELECT [total size KB], B.rows, O.name FROM (select sum(convert(de...

Transferring data between worksheets using Sheet Command?
Hi for an assignment i have to enter grades for 200 students in 4 subjects. there is a front summary sheet that contains all the subjects and all the students and their overall grade GPA etc.. anyway this summary sheet has to be populated automatically from the individual math, english etc.. worksheets. The guide says to do it using the 'sheet command' any help greatly appreciated. Thanks ...

How do you change the data and input data into a chart on Excel?
Jon Peltier's Excel chart info may be useful to you. http://peltiertech.com/Excel/Charts/ "unclemoko" <unclemoko@discussions.microsoft.com> wrote in message news:9AF9FAFA-B657-4C21-A4CA-56B81EC5C27B@microsoft.com... > ...

user-defined data type capabilities
It seems I use Microsoft Visual Basic 6.5 from Microsoft Excel 2003. I've just introduced my first user-defined data type (UDT) to a module. I found it greatly simplified passing parameters. Private Type Journey Depart As String Arrive As String End Type What can I do with such UDTs? 1) Declare functions and scalar, array, and parameter variables. 2) Assign e.g. Dim A as UDT, B as UDT: A = B 3) Not compare variables. e.g. if UDT0 = UDT1 ... gets an error. What else can UDTs be used for? When, in the Visual Basic Editor (VBE), I hover over code referencing a v...

Changing data from imported .txt file
When i import data from an old (dos based) program negative numbers appear with the minus behind the number. Does anyone have any idea how i can change them all from 1,234.56- to -1,234.56 so Excel will recognise them? Changing them by hand takes forever. Thank you. If you're using xl2002+ and are using File|Open to open a text file, you'll see a wizard popup. On step 3 of that wizard, there's an advanced button. Click it. There's an option to treat trailing minus signs as negative numbers. If you already have the data in the worksheet, you can use... Dana DeLouis pos...

moving data from one sheet to another
I have records in sheet1 as follows: A B C D E 1 NAME SEX SUBJECT COLLEGE WHETHER SELECTED 2 A MALE MATHS XAVIER YES 3 B FEMALE ENGLISH SEBASTIAN 4 C FEMALE MATHS PAULS 5 D MALE PHYSICS XAVIER YES 6 E MALE CHEMISTRY PAULS YES 7 F MALE PHYSICS SEBASTIAN YES 8 G FEMALE MATHS XAVIER YES 9 H MALE PHYSICS PAULS 10 I MALE MATHS SEBASTIAN Now, I want to scan entire data upto last cell of the range and move(cut and paste) those of the selected candidates, Sheet2 of the same book.After moving, the name o...

Backing out of the original RECV distributions in RM.
Is there any way to make GP back out of the original AR accounts used on the RM invoice after fully applying a cash receipt to it? For example when posting an invoice using an account that is not the same one as on the customer card, Great Plains will never reverse it even after the document is fully paid. Thanks ...

How do I change headings for columns from numbers to letters? #2
...

Newbie needs help with table
I have created a database with two tables: Product Orders The product table contains the following fields: ProdID Descr OnHand the orders table contains: CustID ProdID QTYOrdered Cost When I created the Orders table I wanted to use the ProdID on the Product table as a lookup field. When I created the lookup type I selected from the Product Table the ProdID and Descr. When I try to do data entry the system does not properly input the data into the orders ProdID table. What I want is to display the ID AND the Description but I only want the ID placed in the or...

downsampling from 16 bit to 8 bit data with colesafearray
I want to downsample a 512x512x16 bit array to a 512x512x8 bit array. I want to do it with COleSafeArray.... here's what I've got already. What should I do to pick off the most significant bits of the 16 bit value and store them in an 8 bit value that I can write to the other array? Also- any ideas on autoscaling data when I do this? If I know the max and min of the source, 16 bit data? Thanks in advance! -spencer // set up our arrays DWORD numElements[] = { 512,512 }; COleSafeArray Array16; COleSafeArray Array8; Array16.Create(VT_UI2, 2, numElements); Array8.Create(VT_UI1, 2, ...

Adding a button to a Wizard
I must be overlooking something really simple, but I just cannot see it... I have a wizard project in which I'd like to create a CButton dynamically for the main "sheet". The button may exist on all subsequent pages swapped in, or could be hidden. I get the button to appear (it gets drawn over a bitmap background), and it does respond to the enter key (when in focus) as my OnMyButton method gets called. However, clicking it with the mouse doesn't get me anything? I create the button as follows, for reference: aRect.SetRect(20,540,80,600); m_TestSheetBtn.CreateEx( NULL, ...

Pivot table grouping (URGENT!)
Hi All, I have a pivot table which has currency amounts in the column and a count of claims in the data area. I have grouped by 100, starting at 0 running to 600. The only thing I'm questioning is when it gets to 200 I think its including any spend in the group 100-200 then again in the group 200-300. Is there anyway I can get the grouping to run from 0-100, then 101-200, 201-300 etc? Please advise -- Adam ----------- Windows 98 + Office Pro 97 When you group by 100, the groups shouldn't overlap. If you start at 0, it should automatically group by 0-99, 100-199, etc. Adam w...

getting updated info from pivot tables
Hi everyone, I created a pivot table with months as column and sum of clients an sum of members as rows. I take the last month and Grand total number to another table I use for reporting. Is there a formula that I ca use to tell the table to get the last month (grand total - 1) and gran total numbers so when I update the pivot I dont have to change the othe table all the time. Thanks for the help, Marco -- marksuz ----------------------------------------------------------------------- marksuza's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=265 View this ...

Auto-send pivot table per user
I have a pivot table which is the performance benchmarking per user. Now what I am doing is double click on user name to drill down their performance per job, copy and plaste it on email, type the email address according to address list, and send to them one by one. Is there any built-in function or VBA/ macro to do these steps with one click? Try Ron de Bruin's website, I needed to do something similar, where I had a pivot table and I needed to email each page field of a pivot table to a different email recipient. The website is as follows:- http://www.rondebruin.nl -- Gary Bro...

combination stacked column and line data
Help. I've been asked to create a chart that has two sets of data that are stacked (columns) and 5 sets of data that display in line format. Is this an option in Excel. I can do a columan and a line but can't seem to add more than that. I'm using Excel 97 (but have access to Excel 2003). Please and thanks. Don't spend a lot of time looking for every last permutation in the "built in custom" chart types. What you probably want isn't there, but it's easy to roll your own type. Make a chart with all data plotted in one format (probably line, so you ha...

Adding a Crystal Report to the RMS reports folder
I can create a Crystal Report for the RMS but when I try to veiw the report I get a error message. What are the steps I need to follow to add the report to RMS so the user can view it Hi..richuscav, You have to make <reportname>.def file along with <reportname>.rpt file and put it in CrystalReport directory of store operations. You can have a reference of dafault DEF files in that directory. If the DEF file is correct you will get the report in Manager. I think this subject is already discussed in this forum,,so search for it. Good luck, CP.Dinesh Jumbo Electronics Co.LLC...

adding core modules and service pack
we are adding extender to our current system after installing extender should I rerun the current service pack before running the utilities or can I install the newer service pack and only run the utilities once ...

sorting two columns of merged cells
I have two columns of merged cells. two cells in each column are merged in each row: ie A1 and B1 are merged into one cell, a2 and B2, etc. The next column is the same; C1 and D1 are merged, C2 and D2, etc. Is there any way I can sort these columns? I need a descending sort by col A and B. Im using Excel 2003 Hope this is clear. Jim Don't merge cells. Look in the archives of this group for countless reasons why not; you've just found one of them. -- David Biddulph "bigjim" <bigjim@discussions.microsoft.com> wrote in message news:...

How to import Outlook Contact data into Excel
I have MS Outlook 2002 SP£ and Excel 2002 SP3. I am trying to import contact data from Outlook into Excel where I have created a template for sending out my invoices. Rather than type in data I thought that there must be a way to import the data from my contacts folder in Outlook. Ihave tried via MS Word 'Merge' programme but nothing but rubbish comes out as I cannot enter fields - Word converts my Excel file into its format and scrambles all! Had assumed it would be straightforward - Any sensible and successful ideas anyone? I have Outlook and Excel 2003 so I don'...

Template Wizard with Data Tracking #5
The Template Wizard is not part of excel 2003 and I can't find it on Microsoft's web site as an add in. Is it still available? If not, how can I recreate it? Thanks. ...

Exporting data to Excel from VC++ 6.0
Can anyone suggest me an easy method (ActiveX, or direct APIs) of exporting data to Excel from VC++ 6.0? The one I am using has few limitations which are mentioned below: 1. Can not format the cells, e.g expand the cells to cell text width. 2. If a cell contains more than 256 characters then the fisrt 256 characters are overwritten and the latest characters are only stored. Regards ...