Pivot Tables #7

Before i post my question am I on the correct board to answer my question? 
I've a question on pivot tables and want to make sure I'm in the correct 
place.

Thanks 


0
ljf50a (4)
11/21/2005 5:46:25 PM
excel 39879 articles. 2 followers. Follow

4 Replies
352 Views

Similar Articles

[PageSpeed] 56

Here is my question.

I have a pivot table that does what I want it to except the category field 
that is a column field shows two different figures and I would like the 
final column to calculate the percentage of one against the other.

For example extract of pivot table is like this:

                                Calls answered    Calls resolved

                                            100                    50
                                            100                    100

I would like it to do a percentage calc in the final column

Like this:

                      Calls answered    Calls resolved       %

                                100                    50               50%
                                100                    100            100%

I've torn my hair out but can't get there.

Any pivot table experts out there please?


0
ljf50a (4)
11/21/2005 5:52:11 PM
If both items are in the same field, you can create a calculated item to 
show the percentage.

Select the Category field button
On the pivottable toolbar, choose PivotTable >Formulas>Calculated Item
Type a name for the formula, e.g. %Resolved
In the formula box, type your formula, e.g.
     ='Calls resolved'/ 'Calls answered'
Click OK
Format the new item that appears in the pivot table, to display as percent.

Red Lorakeet wrote:
> Here is my question.
> 
> I have a pivot table that does what I want it to except the category field 
> that is a column field shows two different figures and I would like the 
> final column to calculate the percentage of one against the other.
> 
> For example extract of pivot table is like this:
> 
>                                 Calls answered    Calls resolved
> 
>                                             100                    50
>                                             100                    100
> 
> I would like it to do a percentage calc in the final column
> 
> Like this:
> 
>                       Calls answered    Calls resolved       %
> 
>                                 100                    50               50%
>                                 100                    100            100%
> 
> I've torn my hair out but can't get there.
> 
> Any pivot table experts out there please?
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/21/2005 6:18:14 PM
Brilliant, brilliant

Been struggling with this all this morning - nearly got there before but 
this is the perfect solution.

Thanks so much. 


0
ljf50a (4)
11/21/2005 9:49:41 PM
You're welcome! Thanks for letting me know that you got it working.

Red Lorakeet wrote:
> Brilliant, brilliant
> 
> Been struggling with this all this morning - nearly got there before but 
> this is the perfect solution.
> 
> Thanks so much. 
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
11/21/2005 10:30:12 PM
Reply:

Similar Artilces:

Pivot Tables/Charts and common filter
I have a set of data which is used to generate multiple pivots charts. Each set of pivot charts also runs from different data worksheets. However, for each chart the report filters need to remain the same. I was wondering if you can link the report filters together so that when you make a change to one filter all the filters change to the same selection. Is this possible? Thank you. Is an Excel 2007 VBA solution acceptable? If yes, provide sample data. ...

Transferring emails downloaded from Eudora 7.1 to Outlook 2002?
I just switched to using Outlook 2002, and I was wondering if there is a way I can transfer my emails that I have downloaded from Eudora 7.1 to Outlook 2002. If I have to send the emails to myself again, I will, but let me know if there is a way I can do more than one at a time. I switched because Outlook has the functionality I need, and I want to be more familiar with it, as I will be using it for work. Thanks for any help! Carrie Reed <CarrieReed@discussions.microsoft.com> wrote: > I just switched to using Outlook 2002, and I was wondering if there > is a way I can tran...

Help! Pivot table need to make Pie Chart
Hi, Can anybody help me, i need to make a pie chart after i made the pivot table. Here what i have for data: Year, Month, Company's name, product and Revenue. I need to make a pie chart of What % of total sales did each product group contribute on the same sheet. Francis In the pivot table, put Product in the row area, and revenue in the data area. Remove the other fields from the pivot table Select a cell outside of the pivot table, and click the Chart Wizard button Select the Pie chart type, and one of the subtypes Click Next For the data range, ...

Windows 7 tray manager program list
Hi, Does anyone know how to remove a program from the tray manager's icon/notification customization list? The list shows applications that have been completely uninstalled - I have verified that the program directories and registry entries for them are gone - so there is no possibility that an icon or notification will appear, but the tray manager doesn't seem to be coordinated with the installed program list. George George, There is an easy solution for XP right here: http://www.help2go.com/Tutorials/Windows/Remove_Icons_from_your_System_Tray.html I am at work n...

List of CRM tables?
Hi All, I'm just starting out altering some SQL queries in excel to make the reports more relevant and find myself wanting to pull data from two tables. Does anybody know where there is a list of tables for reference of failing that, which table the property opportunity.partneridname sits in? Thanks D For opportunity, you can look into opportunitybase and opportunitybaseextension table. But you can use the view "opportunity". Hope this helps. Darren Liu Crowe Chizek and Company http://www.crowecrm.com On Feb 22, 12:23 pm, "Dempsey J" <xfo...@hotmail.com&g...

pivot tables ...
Hi I'm trying to use the third option in the Pivot table assistant. I'm using a French version of XL and I'm not sure of the exact wording in English. It's in the section entitled Where is the data, and it's the third option down, and it's something like "Spreadsheet zones with labels". How does that thing work ? On Sheet1, I have this Customer part date Price ABC 1234 12/08/2006 97 ABC 1235 13/08/2006 78 DDD 1236 14/08/2006 57 DDD 1237 14/08/2006 96 DDD 1238 14/08/2006 56 DDD 1239 14/08/2006 55 XYZ 1240 18/...

FRx 6.7 Registration Key Problem
I have an FRx 6.7 install running under Windows 2003 terminal services and when I installed everything, relocated the server, and tested, everything went smooth. I was able to access the system and pull up reports from the catalog. The problem now is that when the user logs in, they get an error that the Spec type isn't correct for this installation type etc, which I've traced back to the fact that the licensing is missing from the installation now. When I re-enter the license, it works fine until I close out of FRx at which point it apparently blows away all changes. This...

Changing Table columns and Rulers
This is a very beginner question on adding Tables to a Word doc. Using Word 2008. I'm trying to center a table and change its indents from the margins. I find that when the cursor changes to the double-arrow one (preparing to drag column borders) then the horizontal Ruler changes to a different zero point. With the change in the Ruler, it is very difficult to make sure the table is centered, the widths of the two columns are the same and the outside "border indent is how I want it. I assume I'm doing something wrong. Thanks for any tips. -- Norm I'...

Copied text opens in table box...Arghhhhhh
Hello... We have just moved over to MS Office 2003 Pro at work and publisher defaults to opening copied text in a table, and not in a text box as required. What seemed a simple procedure in Office 2000 has now become a little confusing, how can I enable any copied text appear in a text box and not a table. I have tried to mirror the work problem on my home computer, but text opens in a text box as required. Thanks This is a known issue and will not be addressed in current versions. The current workaround is to go to Edit > Paste Special > Unformatted Text into the text box. --...

Using Macro to Alter Multiple Pivot Tables
I recently posted a question about "...how to use a macro to alter a Pivot Tabel". Debrah gave me a sollution which worked perfectly :-D Though I wanted more... I tried to alter two Pivot Tables at the same time. And it worked splenditly! :-D Thus I wanted to extend the borders. Can Anybody tell me wheter it is possible to make a Macro which alters the Pivot tables ONLY if the exact match is made with the data of the Pivot Table and the data filled in in the reference cell. The problem is that I use two different databases to fill two different pivot tables; the one thing t...

how to prevent simultaneous access to a table?
Hi, Students can subscribe for any project via a webform. Table 'project ' contains all the proposed project and table 'student' contain the name of the student and the project-id. Now, suppose there is a limit of 4 students for project 'A' and there are already 3 subscribed students for that project. One more student can choose that project. The code checks whether the limit is not reached (by counting the amount students for that project in table 'student') before inserting that student in the table 'student'. My problem is that whe...

Formatting Data Tables in Charts
Can you highlight/fill individual cells, rows, or columns of a data table within a chart? Hi, No. Formatting of the chart data table is very limited. If possible use worksheet cells to display a data table. More info here, http://tushar-mehta.com/excel/newsgroups/data_table/index.htm Cheers Andy Reddo wrote: > Can you highlight/fill individual cells, rows, or columns of a data table > within a chart? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Hi, No. Personally, data table are a real problem, for example if you have a lot of data, they're hopeless. ...

Pivot chart, automatic refresh
Dear All, I have a pivot table with pivot chart, I have to press the re exclamation mark to refresh the chart and table every time I want th situation to be updated. Is there a way that it updates itself at every change that occurs? An how could I switch that auto-refresh function (if it excists) on an off? Your help would be very much appreciated -- Message posted from http://www.ExcelForum.com This is an acknowledged issue with pivot tables and pivot charts. When they are refreshed, any custom formatting is lost. You can read about it here: XL2000: Changing a PivotChart Removes Seri...

Windows 7 Desktop Thinks Shared Printer is Offline
Hello, I have a usb printer attached directly to a brand new windows 7 desktop. I have another desktop that uses my wireless network to access the shared printer. this desktop also uses windows 7. I have a windows vista 64 bit laptop that also uses the wireless network. everything prints fine from the vista laptop and the desktop that is directly connected to the printer. unfortunately, the other windows 7 desktop thinks the printer is offline. I have tried removing the printer from the "remote" desktop and reinstalling using the homegroup feature. This has not...

E-mail Address Table
Does anyone know what table Dynamics Holds the Customer Ship to e-mail address information? Looks like colnetaddrs but I can't locate it on the sql side. SY01200 "MBSNewbie" wrote: > Does anyone know what table Dynamics Holds the Customer Ship to e-mail > address information? > Looks like colnetaddrs but I can't locate it on the sql side. > > > ...

Make Excel Table fit into Word Doucument
Help - I need to paste an Excel table into a portrait-form Word document. Is there any way I can do this? A regular cut and paste leaves half the table off the document. Thanks. Hi R, If you're using Word 2003 and Excel 2003, once you paste the Excel spreadsheet into Word I think it may then become an "object" that you should be able to resize using the sizing handles (the white circles surrounding the object). Even if you can't see the entire Excel spreadsheet on your Word page, if you click on it, you should see those white sizing handles (circles) around it that wil...

Pivot chart Refresh wrecks HAVOC!
When I'm using a pivot chart graph to track stuff, any time I update the spreadsheet and refresh the Pivot Graph, all the labels go back to default. I like them Above the data point, and Aligned at 45 degrees, for maximum readability. How do I keep it this way even when I hit that Refresh button? Also, how do you use Pivot Table fields that are not contiguous? In other words, I have Dates in Column A, which I want to use with data in Column C, D, H and so forth, without copying Column A and putting it next to each of these other columns. Thanks, everybody. Arlen Hi, This i...

Storing an Id in multiple tables
I have a database that has two tables, Leases and Payment History. I need the Autonumber "Lease Id" to store as a new record under a field "Lease Id" in the Payment History table(thereby creating a new payment Id). That's not a recommended approach. There's no point saving a dummy record in the Payment History table. Don't create the new record until you have something to store in it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Joshua Beel" <JoshuaBeel@discussions.microsoft.com> wrote in messag...

Table Notes
Hi I have a database schema in Visio 2002 pro to which I have added notes for the fields in each table. However, I do not seem to be able to print or extract these notes. Apparently you can only do this with enterprise edition. I cant believe it would allow you to add notes without being able to use them. Can anyone help ? Thanks Rob ...

Populating 7 day calendar with information
I wish to add a 7 day calendar to the switch board form, but I'd like to populate each of the day boxes with fields from a query. In essence the query locates things to do in the week. For ease of the user i'd like them to be able to see a 7 day calendar with the fields "ownerid","make","model" based upon the value in "next_due" (eg "next_due" is during that week) Thank you A Flex Grid Control will probably do what you want. Have a look at - http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex%20Grid%20Demo.m...

Remote Desktop Client 7.0 for Windows Server 2003
The latest Remote Desktop Client was released only for XP and Vista, but I'm using it on Server 2003 with no issues (I've extracted and copied files manually, as update.inf and catalog doesn't match operating system). Is possible to make a installer compatible with Windows 2003? Description: http://support.microsoft.com/kb/969084/en-us Update: http://www.microsoft.com/downloads/details.aspx?FamilyId=72158b4e-b527-45e4-af24-d02938a95683&displaylang=en Thanks! :) Hi Tyler, I think 7.x version is only for Win7/Vista/2008 systems. you can use the RDP c...

Excel 2007 - Pivot Table - Conditional Formatting
I have a pivot table and everything is working great except for one problem. I have some conditional formatting on sum of numbers that works great. The problem is if I try to do conditional formatting based on the % of Total it is not working correctly. From what I can tell when I do conditional formatting based on the % of Total it appears it is actually still referencing the sum and not the %. For example: I have a sum of 52 somewhere in the data of the pivot table and the % of Total for that sum is 11%. If I do conditional formatting on the sum of 52 it works fine but if I do conditional f...

Help writing function/routine for creating table
Hi any helper: I have an Excel table: DocNum Test1 Test2 Test3 Test4............. TestN Doc1 x x x Doc2 x Doc3 Doc4 x Hi......... If you're really seeking some help, we'll need a little more than this to go on. You need to tell us what you want to accomplish, what you've tried that did not work, how it did not work, etc etc.............please post back with some more information. Vaya con Dios, Ch...

List of open accounts with Pivot table
Hi ther Excel 2003: I would like to create a list of open accounts receivable with Pivot. My columns are Date, Client, Invoice No, Amount open. I want Excel to list all invoices open only (column "Amount open" does not equal 0), not all invoices. Unfortunately Excel delivers all invoices including the ones with amount = 0. Any suggestions anyone Stefan You can add a column to the source table, in which you calculate the account status. For example, if the Amount data starts in J2, enter the following formula in row 2 of the Status column: =IF(J2<>0,"Open",&qu...

Loop through Table while Renaming Files
Adapted from: http://www.fmsinc.com/FRee/NewTips/Access/accesstip31.asp I need to loop through the a table to make the following changes: If [Photo1] Like "Photo*" Then fOK = RenameFileOrDir([Path1] & [Photo1], _ [Path1] & IIf([K_PID] Is Null Or [K_PID] = "" Or [K_PID] = " ", _ [Path1] & [TAXPINNO] & "_" & [Round] & "_P1.jpg", _ [Path1] & [K_PID] & "_" & [TAXPINNO] & "_" & [Round] & "_P1.jpg")) End If ...