Percentages in Pivot Tables

I have two pivot tables in my worksheet - one is calculating the % value 
correctly, one isn't.  Basically the one that isn't is a pivot table 
summarising data from three files.  The % from the 'Grand Total' row is 
actually adding up the percentage column, instead of dividing the two earlier 
columns.
I don't think I am making myself too clear, but if there is someone who 
thinks they could help me and wouldn't mind looking at my spreadsheet, I 
would be really grateful as I am tearing my hair out!
0
9/19/2005 4:06:12 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
291 Views

Similar Articles

[PageSpeed] 30

Is the % column coming from your source data?  If so, instead of using the 
field from your source data, try inserting a calcuted field for the % column 
directly into your pivot table.  
Using the Pivot Table toolbar, go to Formulas--Calculated Field.

Create the calculation using the previous two columns.  If I'm understanding 
your question, this should work.

Jeff


"ajames" wrote:

> I have two pivot tables in my worksheet - one is calculating the % value 
> correctly, one isn't.  Basically the one that isn't is a pivot table 
> summarising data from three files.  The % from the 'Grand Total' row is 
> actually adding up the percentage column, instead of dividing the two earlier 
> columns.
> I don't think I am making myself too clear, but if there is someone who 
> thinks they could help me and wouldn't mind looking at my spreadsheet, I 
> would be really grateful as I am tearing my hair out!
0
goofy11 (11)
9/19/2005 5:06:05 PM
Reply:

Similar Artilces:

Pivot Table Comparison
Hi all, I am hoping t get some help on this. I have two worksheets of monthly data (last month and current month) that gets updated at the end of each fiscal month. I pull the data into two pivot tables on the third sheet and am trying to put a formula together to compare the pivot table data. Here is an example of the data in each pivot table: In the old file: Charge Number 1234 was used for resource 12 ($300), resource 34 ($1,000) and resource 56 ($600). In the new file: Charge number 1234 was used for resource 12 ($800), resource 34 ($1,400), and resource 56 ($25...

Pivot Tables? #2
I currently use pivot tables quite often and I have a situation where I think a Pivot table would be the best solution but I am not sur eif it is possible, or how to do it if it is possible. I have some data which comes from my financial system through a query. I have to run several different queries to get all of the information I need because of duplicate rows. The first query contains an employee name, check #, check date, net pay, gross pay, total employee deductions and total employee taxes for the pay period. The second query contains employee name, check #, check date and the amount ...

Adding to Table from Drop Down Box
Hello.... In a victim services database, there is a field for the name of an attending police officer. To keep the name format consistent, the drop down box is populated by a table using an SQL statement in the rowsource property. What I want to do is for the user to be able to add another name using the drop down box directly. I have set *limit to list* to NO, and it will modify the record, but it does not add the new name to the table itself. How might I do this. Perhaps some VB code in the afterupdate event? Thanks -- Regards, Richard Harison http://blogs.techre...

percentages...
If I want to make a spreadsheet with the number of times a player has turned out for his team in a season, what is the formula to put in the cell. For example, John Philips has made 15 appearances out of a possible 22. I do not want to put in the formula sum=15/22*100 as I wish to simply tupe 15 in the cell and get it to change the cells contents to 68%. I know how to get it to put this figure into another cell, but how do I get it to do it in the same cell as the info I type. Is this possible? Secondly, if I have the said mr.Philip' percentages for the last say eight years, how can I get ...

Can GETPIVOTDATA refer to the pivot table name?
Is there any way to use the pivot table name (from the table options dialog)? I want to make my report more maintainable and robust ie If I could say GETPIVOTDATA("Key","Maths Exam",.....) it would be more readable than GETPIVOTDATA("Key",Pivot!$e$4,......) Thanks in advance for any assistance. GB You can't refer to the pivot table by name, but you could name a cell in the pivot table, and refer to that name. For example, name cell E4 as Math_Top and the formula would be: GETPIVOTDATA("Key",Math_Top,.....) Green Biro wrote: > Is th...

tax percentage field Functionality to automatically calculate taxe
Microsoft Dynamics CRM 3.0 should include a tax percentage field or the functionality to automatically calculate taxes. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=a287...

Junction Tables
I'm just curious if it makes sense to use junction tables in this instance: student - studentID, studentfirst, studentlast, gender, age (adult, teen, or child), lessonday, status (current or retired) address - addressID, address, city, province, postalcode phone- phoneid, phonenum, extension, phlocation email - emailid email, emailLocation gaurdian – gaurdianid, gaurdianfirst, gaurdianlast, relationship studentaddress - studentID, addressID studentguardian - studentID, guardianID GuardianAddress - guardianID, addressid Studentphone - studentID, PhoneID Guardian...

Pivot Table Wizard
Does anyone know which .DLL file runs the Pivot Table Wizard? I believe I have a Office 2K PC that may need an update. It keeps generating an Excel Error Message: "Problem obtaining data" when using the wizard in Excel after Access creates the pivot table. Thanks, Phil Hi don't think an update will help. This sound like a 'normal' application error. Have you checked the database connection, etc. -- Regards Frank Kabel Frankfurt, Germany Phil wrote: > Does anyone know which .DLL file runs the Pivot Table > Wizard? > > I believe I have a Office 2K PC ...

Can I create an auto refresh pivot table for novice users?
I am attempting to create a spreadsheet that would be used by novice users to record their time spent at various locations. This information would then be printed and forwarded to HR for payroll. I can't rely on the spreadsheet users to refresh the data, can I create a form control or something to refresh the data before printing? ...

percentages
Hi people, I'm new to this excel stuff. I am trying to make a 12-month budget plan, and one of the tasks was to increase an expense by 5%. So like for january I start with $50 and then I have to increase the next month by 5%. Is there a formula for this? I need help!! ASAP!!! Thanks. "Dee" wrote: > So like for january I start with $50 and then I > have to increase the next month by 5%. Is there > a formula for this? Yes. If the Jan amount is in A1, and Feb should be 5% more, computed in A2, the formula would be: =ROUND(A1*(1+5%),2) If you copy t...

Update Pivot Table #2
Is there a way I can get my pivot tables to automatically update before I print the sheet containing them Thanks You can use the before print event You can record a macro when you refresh the tables, then copy and paste that code Private Sub Workbook_BeforePrint(Cancel As Boolean) 'your recorded refresh code End Sub It goes into ThisWorkbook (right click the excel icon next to the File menu and select view code -- Regards, Peo Sjoblom "Brandt" <bsaxey@pridehomesofutah.com> wrote in message news:A1DE0F32-AD26-45B7-AB3D-CC921DB6633A@microsoft.com... > Is there...

Autoformat in pivot tables
Does anyone know if it is possible to remove an autoformat from a pivot table once the document is saved? I would like to return the pivot table to its original form before the autoformat. Thanks. Select a cell in the pivot table, and choose Format>AutoFormat Scroll to the bottom of the list of AutoFormats, and click on PivotTable Classic Click OK If you had previously applied one of the Report AutoFormats, e.g. Report4, any column headings would have been automatically moved to the Row area. You'll have to manually move those fields back to the Column area. C Tate wrote: > D...

Pivot Table Columns
I have a two rows of labels but the second row of labels I want them in one column not across as a column heading as I am using the column headings for another purpose. We have two names for our salons the first is a source code from an older system and the second is the real name I want to list both together in the row labels but across from each other not below. Any suggestions: example: BEATYB BEAUTY BEAT SALON CASPER CASPER'S HAIR CARE thanks to who can help Hi, Just place both of them in the row field area. If you are using Excel 2007, roght click, ...

Naming conventions
Hi, Another bog standard question. I have always used lowercase with underscores for identifiers (this is what BOL uses in 2000). Is this still usual or is Pascal casing more commonly used in SQL these days (as the BOL in 2005 uses)? Is camel case ever used in SQL? Cheers, James The lowercase and underscores are the ISO convention and it has actual testing behind it. Get a copy of SQL PROGRAMMING STYLE (Morgan- Kaufmann, ISBN: 978-0-12-088797-2) for details. JimLad (jamesdbirch@yahoo.co.uk) writes: > Another bog standard question. I have always used lowercase with > ...

percentages
How do you format a percentage in the last column? The first column consists of a total, the in-between columns are totals for the day, and the last column takes the first columns total and subtracts all the columns in between and gets a total. I need the last column to be a percentage of the first column. I want it to tell me a percentage of the orginal total. For example I need to know when the orginal total is down to 50%. Hope someone can help me. Thanks Jo Newkirk:) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet ...

Sample Data for Pivot Table
Is it possible to retrieve Pivot Table Sample Data from Excel online? I have tried searching for this to no avail. Hi, go to Debra web at the end of the page you will find the download http://www.contextures.com/xlPivot11.html "Susancpa" wrote: > Is it possible to retrieve Pivot Table Sample Data from Excel online? I have > tried searching for this to no avail. ...

Pivot Tables #2
Where can I find simple instructions/examples on using Pivot Tables? Is there a good book or a good website to consult? Candace Try Jon's site: http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm Andy. "Candace" <atty7414@bellsouth.net> wrote in message news:010401c37c6d$f3d41d70$a301280a@phx.gbl... > Where can I find simple instructions/examples on using > Pivot Tables? Is there a good book or a good website to > consult? Some more, well one redundant: Debra Dalgleish's pictures at Jon Peltier's site: http://www.geocities.com/jonpelt...

Macro to create a Pivot Table
Does anyone have an example of a macro that automatically creates a Pivot Table from raw data or automatically updates that Pivot Table with the raw data? You'll get a macro that's specific to your workbook if you turn on the macro recorder as you manually create and refresh a pivot table. Attach the macro to a toolbar button, and run it when required. David McRitchie has information on getting started with macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Chris wrote: > Does anyone have an example of a macro that automatically > creates a Pivot Table from r...

Percentages? How?
I am currently running a price guide that looks as follows: A B C D E F Item Price Cost SRP GP% Retail Unit Column C is the Customer's Cost per Retail unit (F) Column D is the Suggested Retail Price Column E is the Gross Profit Percentage Column E is =(D1-C1)/D1 to arrive at the GP%. *Now* I want to reverse the process! I need to type a percentage into E and have D pop up automatically. (I.e., instead of typing in an SRP of $2.50 on an item that costs $1.36 to arrive at a GP of 45.6%, I want to type 40% and find out what the SRP woul...

Excel 2007
In Excel 2003, if I had a pivot table open, I could go to a new workshhet, create a new pivot table and one of the choices for the source data would be another pivot table in this workbook. This would create a new pivot table using the same pivot table cache. I can't seem to find the equivalent functionality in Excel 2007. Can this still be done? Tom I believe this is now deprecated, sorry -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS web: www.nickhodge.co.uk UK Excel User Conference In Cambridge November 28th - December 1st 200...

Pivot Table Help
Hello, I have a challenging pivot table issue that I hope I can get some help on. Here is the spreadsheet I'm working with: https://www.yousendit.com/download/OHo0N3RSSU90d0UwTVE9PQ I'm having problems with showing percentages in my pivot table such as gross margin and operational profit. Here is the P&L pivot table as I have it now, as you can see I can't get the "Gross Margin %" to output a number. Both "Gross Margin %" and "Operational Profit %" are calculated fields I've added to the pivot table since the raw data only inc...

Passing Parameters to a Pivot Table
Hi, We use parameters and Excel formulas (Start Date=Now()+1 year, End Date=Now(), etc.) to pull sales detail from an IBM system and it works fine. I want to use a piivot table to summarize 14,000+ lines of details into about 800 parts. Works great but I need to change the date range daily to capture 1 year of history. This is one of 12 different queries in the same file we want to do this with. Is there a way to use parameters in a Pivot Table like we are now doing with an MS Query? On Mar 11, 6:37=A0pm, Keith <Ke...@discussions.microsoft.com> wrote: >...

How to remove indent in table cell from Excel
I copied part of an XLS spreadsheet to create a Word table. The text in many cells is indented in the original, but I need to make these flush left within the cell. But I can't find a place within Word where this indent is shown: it's not in the table cell specs nor in the paragraph definition. Is there a way to unindent? My purpose is to do this within VBA, in case that changes the answer. -- Christopher Brewster Lockheed Martin, Eagan MN Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-word-general/200911/1 Dim i As Long, j As Long Wit...

Data Tables
Hi Not sure if Data Tables are what I should be looking at to solve this problem, perhaps I need a Macro. I have a set of Data in rows from (including headers) A1:E400. In column E, I have numeric values to 1 decimal point. This value is not unique but has a lower limit of 2.0 and higher limit of 9.0. What I am trying to do is to specify a value in my input cell of (say) 3.5 and have Excel return the contents of the entire row (A to E) for each occurrence of that value (3.5) in my data range (A2:E400). Can I use a Data Table to do this? Any suggestions gratefully received. Cheers :confus...

percentage help
Hi Guys. I am trying to calculate a percentage increase based solely on previous and expected percentages. Out of 100% of all orders 20% of them are in system 1 and 80% in system 2. We currently have delays with a total of 13% of all orders within the systems. 4% of the delays are in system 2 and 9% of the delays are in system 1. In 4 months time we will be moving from having 20% of all orders in system 2 to having 60% of all orders in system 2. Based on this what % of orders will have delays and what will the split be. ---- Current usage delays Total % 100% 13% System 1 20% ...