How can I Show data as a % of Sub Group in a Pivot Table

I want to show a column of data (% of City) as a % of a sub group
total.  Here's my example of how the pivot table should look:

State   City       Carrier    Sq Yards    % of City (Sq Yards2)
Alabama
        Birmingham
                   Carrier 1   10          20%
                   Carrier 2   20          40%
                   Carrier 3   10          20%
                   Carrier 4   10          20%
        Birmingham Total       50         100%
        Mobile
                   Carrier 1   10          25%
                   Carrier 2   10          25%
                   Carrier 3   10          25%
                   Carrier 4   10          25%
        Mobile Total           40         100%

I can't get the last column to calculate.  The last column(Sq Yards2)
is simply a duplicate of Sq Yards, however, it is displayed as a % of
column.  I don't want Column to show, I want a % of City shown.  How
can I do this in a pivot table ??

0
3/16/2005 8:52:46 PM
excel 39879 articles. 2 followers. Follow

4 Replies
649 Views

Similar Articles

[PageSpeed] 41

You can add columns to your data table, and calculate the total for each
state, and the percent of state total for each row. For example, with 
your data in cells A1:D200 --

State    City        Carrier    Sq Yards
Alabama  Birmingham  Carrier 1    10

In cell E1, type:  PctCity
In cell E2, type:  =D2/SUMIF($B$2:$B$44,B2,$D$2:$D$44)

Copy the formulas down to row 200

Change the Pivot table source to include the new column
Refresh the pivot table
Add the PctCity to the Data area
Format the field as Percentage

Note: State total and Grand Total will show an incorrect amount in this 
column.


Faulk wrote:
> I want to show a column of data (% of City) as a % of a sub group
> total.  Here's my example of how the pivot table should look:
> 
> State   City       Carrier    Sq Yards    % of City (Sq Yards2)
> Alabama
>         Birmingham
>                    Carrier 1   10          20%
>                    Carrier 2   20          40%
>                    Carrier 3   10          20%
>                    Carrier 4   10          20%
>         Birmingham Total       50         100%
>         Mobile
>                    Carrier 1   10          25%
>                    Carrier 2   10          25%
>                    Carrier 3   10          25%
>                    Carrier 4   10          25%
>         Mobile Total           40         100%
> 
> I can't get the last column to calculate.  The last column(Sq Yards2)
> is simply a duplicate of Sq Yards, however, it is displayed as a % of
> column.  I don't want Column to show, I want a % of City shown.  How
> can I do this in a pivot table ??
> 


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

0
dsd1 (5911)
3/16/2005 9:12:02 PM
Your Awesome!!!!  That worked.  Thanks for the help!

Debra Dalgleish wrote:
> You can add columns to your data table, and calculate the total for
each
> state, and the percent of state total for each row. For example, with

> your data in cells A1:D200 --
>
> State    City        Carrier    Sq Yards
> Alabama  Birmingham  Carrier 1    10
>
> In cell E1, type:  PctCity
> In cell E2, type:  =D2/SUMIF($B$2:$B$44,B2,$D$2:$D$44)
>
> Copy the formulas down to row 200
>
> Change the Pivot table source to include the new column
> Refresh the pivot table
> Add the PctCity to the Data area
> Format the field as Percentage
>
> Note: State total and Grand Total will show an incorrect amount in
this
> column.
>
>
> Faulk wrote:
> > I want to show a column of data (% of City) as a % of a sub group
> > total.  Here's my example of how the pivot table should look:
> >
> > State   City       Carrier    Sq Yards    % of City (Sq Yards2)
> > Alabama
> >         Birmingham
> >                    Carrier 1   10          20%
> >                    Carrier 2   20          40%
> >                    Carrier 3   10          20%
> >                    Carrier 4   10          20%
> >         Birmingham Total       50         100%
> >         Mobile
> >                    Carrier 1   10          25%
> >                    Carrier 2   10          25%
> >                    Carrier 3   10          25%
> >                    Carrier 4   10          25%
> >         Mobile Total           40         100%
> >
> > I can't get the last column to calculate.  The last column(Sq
Yards2)
> > is simply a duplicate of Sq Yards, however, it is displayed as a %
of
> > column.  I don't want Column to show, I want a % of City shown.
How
> > can I do this in a pivot table ??
> >
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

0
3/17/2005 7:12:38 PM
You're welcome! Thank you for telling me that it worked.

Faulk wrote:
> Your Awesome!!!!  That worked.  Thanks for the help!
> 
> Debra Dalgleish wrote:
> 
>>You can add columns to your data table, and calculate the total for
> 
> each
> 
>>state, and the percent of state total for each row. For example, with
> 
> 
>>your data in cells A1:D200 --
>>
>>State    City        Carrier    Sq Yards
>>Alabama  Birmingham  Carrier 1    10
>>
>>In cell E1, type:  PctCity
>>In cell E2, type:  =D2/SUMIF($B$2:$B$44,B2,$D$2:$D$44)
>>
>>Copy the formulas down to row 200
>>
>>Change the Pivot table source to include the new column
>>Refresh the pivot table
>>Add the PctCity to the Data area
>>Format the field as Percentage
>>
>>Note: State total and Grand Total will show an incorrect amount in
> 
> this
> 
>>column.
>>
>>
>>Faulk wrote:
>>
>>>I want to show a column of data (% of City) as a % of a sub group
>>>total.  Here's my example of how the pivot table should look:
>>>
>>>State   City       Carrier    Sq Yards    % of City (Sq Yards2)
>>>Alabama
>>>        Birmingham
>>>                   Carrier 1   10          20%
>>>                   Carrier 2   20          40%
>>>                   Carrier 3   10          20%
>>>                   Carrier 4   10          20%
>>>        Birmingham Total       50         100%
>>>        Mobile
>>>                   Carrier 1   10          25%
>>>                   Carrier 2   10          25%
>>>                   Carrier 3   10          25%
>>>                   Carrier 4   10          25%
>>>        Mobile Total           40         100%
>>>
>>>I can't get the last column to calculate.  The last column(Sq
>>
> Yards2)
> 
>>>is simply a duplicate of Sq Yards, however, it is displayed as a %
>>
> of
> 
>>>column.  I don't want Column to show, I want a % of City shown.
>>
> How
> 
>>>can I do this in a pivot table ??
>>>
>>
>>
>>--
>>Debra Dalgleish
>>Excel FAQ, Tips & Book List
>>http://www.contextures.com/tiptech.html
> 
> 


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

0
dsd1 (5911)
3/17/2005 7:19:07 PM
hey you can do it as part of the pivot table if you wish
by adding the value field to the pivot table again and double clicking
on it, you then have a host of options on how to calculate the data

0
sgjones73 (5)
3/17/2005 9:20:20 PM
Reply:

Similar Artilces:

Can I fade the edges of photos that I use?
I am trying to use photos in a publisher document that I want to fade the edges of, is this possible to do with publisher? -- D:o) krazy-4-coke wrote: > I am trying to use photos in a publisher document that I want to fade the > edges of, is this possible to do with publisher? Not really. You should use dedicated image manipulation software for this, such as Photoshop Elements, Paint Shop Pro, or Microsoft Digital Image. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org Ed, Are any of these programs typically come loaded on a computer?? I have another question - ca...

error in importing data
Sorry the error is 0x8004032d ...

Re: Extracting Exchange User Data From AD 02-24-10
If you do not like scripting, you can try GAL Exporter or Fast User Manager & Reports from IMIBO - http://www.imibo.com > > "Ringholz, Blake" <bringholz@nospam.com> wrote in message > news:76AEFC2F-85A7-4666-8262-27FB0737D09A@microsoft.com... >> Hello All - >> >> I need to get an Excel Spreadsheet that lists everyone first name, last >> name, email address, job title, etc pulled from Active Directory. Is >> there an easy way to do this? >> >> Thanks, >> Blake > > > > > ...

Pivot Table Cycling Through Page Fields Automatically
Hi. I am trying to cycle through a complete set of data in one of the parameters in the "Page" field. For example, there are 500 investments, and I want to compute the internal rate of return (IRR) for each investment based on a series of cashflows for each investment. The IRR is a function that is placed outside the pivot table. As each investment number is chosen, the underlying pivot table cashflow data changes, allow the IRR function to pick up these cashflows and compute the IRR. However, if there are 500 investments, this becomes very time consuming - especially if the...

pivot table #21
I have a question on Excel. When I update a pivot table, I used to be able to hold down the shift and ctrl keys and highlight the area, but lately I found that I cannot use this short cut method. Is there another short cut method? Thanks for your help. In step two of the Pivot Wizard, you should be able to select a starting cell on the worksheet, then hold the Shift key, and tap the End key, then the Down or Right arrow key, to select a range of cells. Or, base the pivot table on a dynamic range, which will expand automatically as new records are added. There are instructions here:...

Can I delete my switchboard and make a new one?
I do not like my switchboard and would like to delete this one and start all over. But when I did delete it I couldn't make another one, because I couldn't delete the default. What should I do?Thanks,Dustin Just delete the form and the table that were created by the wizard, then start over.-- Rick B"Dustin" <Dustin@discussions.microsoft.com> wrote in message news:42F94A44-9352-4D02-9104-65593869B271@microsoft.com...>I do not like my switchboard and would like to delete this one and start >all> over. But when I did delete it I couldn't make another one, beca...

Outlook options not showing up for user
I am in the process of training and testing with our CRM 3.0. I have installed either the laptop client or the desktop client on the user's machines. For training purposes I have also installed CRM on our training room pc's. I set the user's Outlook for that particular pc and then installed CRM with the user logged on, but I ran the installation under run as. The installation seemed to go fine, yet the user's cannot see and folders or menu when they launch their Outlook. The can get to it in the web environment though. Any ideas on why this has happened and what I can d...

Doing Analysis from large amounts of DATA
Hi all, I have an attachment that's apart of this message & within it I have 3 tabs 1) Revenue 2) Expenses & 3) Net Position. I would like to do some Analysis on the above three thou a way where I can manipulate the Data. How would I go about doing this? I've tried a Pivot Table thou it didn�t work out in the sense that it was messy & just failed in calculating stuff, not sure if I did it right thou.. Would really appreciate all the help as I actually have about 20 of these sheets which from that fall into 4 groups being 5 sheets per group. I hope this all makes sense, wo...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

Sub Folders
Hi Is there a way to check if a folder has a sub folder and if yes get its name, also to loop until no sub folder is found. C:\Weeks Post\Sundays Post\Mondays Post\Tuesdays Post etc. Create Post array Post(0) = Sundays Post Post(1) = Mondays Post Post(2) = Tuesdays Post something like this, can you help? regards Ron On 24/11/2009 09:52, LondonLad wrote: > Hi > Is there a way to check if a folder has a sub folder and if yes get its name, > also to loop until no sub folder is found. > > C:\Weeks Post\Sundays Post\Mondays Post\Tuesdays Post etc. > ...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

transfer inbox in table format to word
In my older version I could cut and paste the table format in Outlook to a word file. Now I don't seem to be able to do it although I can print the file in that format within Outlook. When I export the inbox to word I get the whole text not just the headings. Is it possible to transfer the table format to a word file? ...

pivot table -repeating "months or days" after grouping
I have data that includes the month, day, hour, min. When I run th pivot table function to reduce the data to hourly I need the date t repeat in the date column. Example: 9/19/2002 0:00 2.7 9/19/2002 0:10 3.7 9/19/2002 0:20 3.8 9/19/2002 0:30 4.6 9/19/2002 0:40 4.8 9/19/2002 0:50 4.8 9/19/2002 1:00 5.6 9/19/2002 1:10 5.2 What I get when I group the Pivot table: Sep 19-Sep 12 AM 2.7 - - 1 AM 3.7 - - 2 AM 3.8 - - 3 AM 4.6 - - 4 AM 4.8 What I want is: Sep 19-Sep 12 AM 2.7 - 19-Sep 1 AM 3.7...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

how can I drag formulas with other cell references
example cell A1 contains formula "=stdev(a2:a7) cell a2 contains formula "=stdev(a8:a13) Is it possible to drag the formula down so that cell a3 contains "=stdev(a14:19) and cell a4 contains "=stdev(a20:a25) and so on for cells a5, a6, a7 etc. etc. Hi Bram, See http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr B1: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) B2: =stdev(OFFSET($A$1,6*ROW()-5,0):OFFSET($A$1,6*ROW(),0)) It was easier to test and verify using SUM instead of stdev, but the formula is same NOTE the formulas are in a different colu...

Create interactive pivot table chart based on item selected
I'm trying to remember how to drag a chart object to the top left cell of a pivot table thus displaying a charted image of the detail item selected. Any suggestions? ...

can i have more sender in outlook setting ?
i using win server 2003 , exchange 2003 , i using exchange client in outlook to send & receive , but my boss have more than one mail address , can i use another AD mail address for send out mail , i have try to add a pop3 , the sender also is computer user thank your ...

Importing data into Outlook #2
Is there a process or program with which I can transfer my ACT! data into Outlook contacts? Thanks! ...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Add a specific Record to a Table based on a check box
I have a Table called ServiceTypes. Based on a User's input on a ProposalForm, ServiceTypes need to be added to a ProposalServicesTable. For instance, I have a Check Box on the ProposalForm. When a Check Box is clicked Yes, Access must search the ServiceTypes Table, select a specific ServiceTypeID, and add the ServiceType to the ProposalServicesTable. How can I add the proper Service record from the ServiceTable to the ProposalServicesTable based on the Check Box? I wouldn't do it that way. I'd use a listbox (with multi-select set to YES) that was sourced to the ServiceTab...

IE8 can't access Microsoft Office site when all other browsers can
When trying to access the Microsoft Office page using IE8 I keep getting the "Internet Explorer cannot display the webpage" message. If I use any other browser (Firefox, Opera, Safari, Chrome) I have no problem accessing the page. This happens whether running in normal or No Add-on's mode. Despite how many people keep complaining about all the problems with IE8, neither the MVP's or Microsoft acknowledge that they exist. Asus P5E Intel E8400 Core2Duo 3.0GHz 4 GB PC2-6400 DDR2 Windows 7 Ultimate (with all the latest updates installed) NIS 2010 (all up to date)...

I can't get rid of 1 "Payment to Send' Reminder-M07
On my Home Page in the Reminder section, I have a "1 Payment to Send" listed there. But there is NO payment to send. I"ve opened up back-up copies as far back as May and the reminder is there, but there is no payment that needs sending. Does anybody have an idea how I can get rid of this Reminder? Just removing the Reminder from Home Page doesn't do it. When you put the Reminder back on the Home Page, the "Payment to Send" is still there. Clicking on the link just takes me to Bill Summary page. ...

Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com Stored procedures don’t add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that ...

is there any way I can trap for the use of navigation buttons?
Hi Is there any way I can trap for the use of navigation buttons, such as Next, Previous and New record? Stapes Probably. Trap what and when? "Stapes" <steve.staple@gmail.com> wrote in message news:7fe0fd6b-0da1-495e-a369-29bc72ca3af9@s8g2000prg.googlegroups.com... > Hi > > Is there any way I can trap for the use of navigation buttons, such as > Next, Previous and New record? > > Stapes On Wed, 16 Jan 2008 05:14:20 -0800 (PST), Stapes <steve.staple@gmail.com> wrote: No. And there shouldn't be a reason for that. There are plenty of events in...

how can I look into store.exe
I have an email sobig that i cant even download it on my outlook express 6.0.... The adviced for is to locate it on the Queues in Outbound or inbound messages on IMC, but if it is not in there the message may be stuck up in Store. How can I locate it and delete the massega in store.exe they say I can use the MFCmapi but i'm a newbie how can do this. Do I have to download the MFCMapi? what is it? thanks ...