Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / March 2008

Tip: Looking for answers? Try searching our database.

pivot table percentages

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
BK - 07 Mar 2008 14:09 GMT
Using XP and Office 2003

I created a pivot table to analyze a group of email address extensions.
(".com" or ".org" or ".gov", etc.)

The pivot table returned the count of each extension type and the grand
total.  Now I want to create an additional column that will calculate the
percentage of the total for each extension type.  I built the first formula
at the end of the first row of data in my pivot table.

The problem I am having comes when I try to copy that formula down or even
when I try to copy and paste special that formula.  I do not understand the
way the formula  displays in the first cell where I built it, so I don't
know how to correct the formula for ease of copying.

Anyone know what I'm talking about??
John Bundy - 07 Mar 2008 14:26 GMT
Without the data it will be hard to explain in detail but this should get you
started. If you select the pivot table, go to Insert in the menu and select
Calculated Field, this lets you insert a column containing your custom
formula. Hope that helps.
Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> Using XP and Office 2003
>
[quoted text clipped - 12 lines]
>
> Anyone know what I'm talking about??
BK - 07 Mar 2008 15:19 GMT
Having a little trouble, so let me give you more info.

my data table has the domain portion of the email address in Column A and
the extension portion of the email address in Column B.  So A2 might read
john@school and B2 might read "com" for example.  All the usernam@domain
portions of the email address are in Column A and all the extensions are in
Column B (.com, .org, .gov, .net, etc.)

My pivot table selects just Column B from the data table.  Then I drag the
column title (extension) to the "drop row fields here" section of the pivot
table and I also drag it to the "drop data items here" portion of the pivot
table.  The result is a list of all the extensions and a count of each one
with a total at the bottom.

Now I want to calculate the percentage of the total for each extension type
so that the row might read: "com > 147 > 72%"

In the cell next to the count of the "com" I put "=B5/B11" and it produces
the correct result even though the formula that actually that appears looks
like this:  =GETPIVOTDATA("ext",$A$3,"ext","com")/GETPIVOTDATA("ext",$A$3)

I cannot seem to copy this formula down so that all the different email
extensions count will show an equivalent percentage of the total.  I guess I
do not understand the dialog box that appears when I try to Insert
Calculated Item.

> Without the data it will be hard to explain in detail but this should get
> you
[quoted text clipped - 22 lines]
>>
>> Anyone know what I'm talking about??
John Bundy - 07 Mar 2008 15:41 GMT
I think this will help you, this site has everything pivot table related. I
think Debra invented them!
j/k

http://www.contextures.com/xlPivot06.html
Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> Having a little trouble, so let me give you more info.
>
[quoted text clipped - 48 lines]
> >>
> >> Anyone know what I'm talking about??
BK - 07 Mar 2008 19:52 GMT
Thanks so much.  I certainly ought to be able to find what I need here.

>I think this will help you, this site has everything pivot table related. I
> think Debra invented them!
[quoted text clipped - 70 lines]
>> >>
>> >> Anyone know what I'm talking about??

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.