Pivot Table Problem #2

I have a pivot table I created that basically counts items 
so that I have am item name and count side by side after 
the pivot table completes as below

Item1  24
Item2  15

Now my issue is that I want to count a secondary attribute 
of these same items (say color for example) and express it 
as a percent of the count already in the pivot table.  I 
have no problem getting the count but I can't seem to 
figure out how to get the percentage part.  I see options 
to have the data as a % of the column or row, but not as a 
percent of the existing count in the table.  For example 
the finished pivot table would be as follows assuming the 
count of the secondary attribute was 6 for item1 and 5 for 
item2:

Item1  24  25%
Item2  15  33%

Is there a way to do this in the pivot table or is this a 
limitation ?

Many thanks in advance.

Rick




0
rick5659 (1)
9/24/2003 5:01:21 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
350 Views

Similar Articles

[PageSpeed] 47

Rick,

Right click on your pivot table, the choose "Formulas", "Calculated Field...."

Then for the formula, use = Color / Item
(Use whatever your field names actually are, of course.)

Color and Item are the field names that appear in the lower box - you need to use the "insert field" button, so type =, then select
Color, hit insert field, type / , then select Item, and hit insert field.

And you're done....

HTH,
Bernie
Excel MVP


"Rick" <rick@cox.net> wrote in message news:0fe501c38258$e5b25b30$a301280a@phx.gbl...
> I have a pivot table I created that basically counts items
> so that I have am item name and count side by side after
> the pivot table completes as below
>
> Item1  24
> Item2  15
>
> Now my issue is that I want to count a secondary attribute
> of these same items (say color for example) and express it
> as a percent of the count already in the pivot table.  I
> have no problem getting the count but I can't seem to
> figure out how to get the percentage part.  I see options
> to have the data as a % of the column or row, but not as a
> percent of the existing count in the table.  For example
> the finished pivot table would be as follows assuming the
> count of the secondary attribute was 6 for item1 and 5 for
> item2:
>
> Item1  24  25%
> Item2  15  33%
>
> Is there a way to do this in the pivot table or is this a
> limitation ?
>
> Many thanks in advance.
>
> Rick
>
>
>
>


0
deitbe (120)
9/24/2003 12:45:48 PM
Rick,

Post a sample data table, maybe 10 lines total, and I'll take a look.

HTH,
Bernie
Excel MVP


"Rick" <rick@cox.net> wrote in message news:1b6b01c382b9$de4d62c0$a001280a@phx.gbl...
> Bernie -
>
> I am having trouble making this work.  I get #DIV/0! when
> I do this. I noticed in the box that I only get my
> original field names.  Shouldn't I have the choice
> of "Count of Items" in order that I might make the calc ?
> I tried putting in coun Any insight into what my issue
> here would be appreciated.
>
> thx,
>
> Rick
>
>
> >-----Original Message-----
> >Rick,
> >
> >Right click on your pivot table, the
> choose "Formulas", "Calculated Field...."
> >
> >Then for the formula, use = Color / Item
> >(Use whatever your field names actually are, of course.)
> >
> >Color and Item are the field names that appear in the
> lower box - you need to use the "insert field" button, so
> type =, then select
> >Color, hit insert field, type / , then select Item, and
> hit insert field.
> >
> >And you're done....
> >
> >HTH,
> >Bernie
> >Excel MVP
> >
> >
> >"Rick" <rick@cox.net> wrote in message news:0fe501c38258
> $e5b25b30$a301280a@phx.gbl...
> >> I have a pivot table I created that basically counts
> items
> >> so that I have am item name and count side by side after
> >> the pivot table completes as below
> >>
> >> Item1  24
> >> Item2  15
> >>
> >> Now my issue is that I want to count a secondary
> attribute
> >> of these same items (say color for example) and express
> it
> >> as a percent of the count already in the pivot table.  I
> >> have no problem getting the count but I can't seem to
> >> figure out how to get the percentage part.  I see
> options
> >> to have the data as a % of the column or row, but not
> as a
> >> percent of the existing count in the table.  For example
> >> the finished pivot table would be as follows assuming
> the
> >> count of the secondary attribute was 6 for item1 and 5
> for
> >> item2:
> >>
> >> Item1  24  25%
> >> Item2  15  33%
> >>
> >> Is there a way to do this in the pivot table or is this
> a
> >> limitation ?
> >>
> >> Many thanks in advance.
> >>
> >> Rick
> >>
> >>
> >>
> >>
> >
> >
> >.
> >


0
deitbe (120)
9/25/2003 11:53:05 AM
Sub DeleteMissingItems2002()
'prevents unused items in XL 2002 PivotTable
'If unused items already exist,
'run this macro then refresh the table
Dim pt As PivotTable

Set pt = ActiveSheet.PivotTables.Item(1)
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone

End Sub      


followed by---


Private Sub Workbook_Open()
Dim ws As Worksheet
Dim pt As PivotTable

On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="MySecretWord"
For Each pt In ws.PivotTables
pt.RefreshTable
Next
ws.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
Password:="MySecretWord"
Next

End Sub


club these two macros together. they worked for me. 

i got help from www.contextures.com - good site by debra for
pivottables.


---
Message posted from http://www.ExcelForum.com/

0
1/14/2004 8:03:04 PM
Reply:

Similar Artilces:

auto table of contents in publisher??
Is there the functionality to create a TOC in publisher that can be linked to Heading styles or something like is avail. in MS Word. thanks in advance Scott scott wrote: > Is there the functionality to create a TOC in publisher > that can be linked to Heading styles or something like is > avail. in MS Word. > > thanks in advance > > Scott Nope. No, create it in Word or do it manually which really is the answer if you want a top class job. -- ...

Excel Functions #2
hi guys, just wondered if anyone knew which function i would use to do this, because im fairly novice with excel: Basically if the cell value is above 110 i want the cell to turn green if the cell value is between 110 and 105 i want it to turn yellow if the cell value is below 105 i want it to turn red any help would be seriously appreciated. thanks spotseven wrote: > hi guys, just wondered if anyone knew which function i would use to > do this, because im fairly novice with excel: > > Basically if the cell value is above 110 i want the cell to turn gr...

RMS 2.0 Integration Approach
Hi everyone, I have Commerce Server 2009 in place and would like to integrate it with RMS2.0. What are some ways in general that an external system can integrate with RMS2.0? Regards. ...

if isblank problem
I have written following formula on technical sheet of my workbook =if(isblank(q31:q36),0,sum(q31:q36)/4) which gives me correct answer true version ie 0 but when try to enter on summary sheet as =if(isblank(technical!q31:q36),0,sum(technical!q31:q36)/4) i get false version any ideas why? Thanks Tina Hi try =if(countblank(technical!q31:q36)=6,0,sum(technical!q31:q36)/4) -- Regards Frank Kabel Frankfurt, Germany tina wrote: > I have written following formula on technical sheet of my workbook > =if(isblank(q31:q36),0,sum(q31:q36)/4) > which gives me correct answer true version ...

Internet Mail Service Problem
This is a multi-part message in MIME format. ------=_NextPart_000_000B_01C49F0B.EE172F50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Team, For quite sometime my Internet Mail Service for Exchange 5.5 get stopped = of its own. My present environment setup is:-=20 1. I have 5 Exchange administrative console located at 3 three = different building.=20 2. My OS is Windows 2000 Server with SP4 3. Exchange 5.5 with SP4=20 4.Running NAV for Exchange built 2.18=20 After going through application logs, I found following E...

New Project, Different Problem: AutoFilter?
I'm creating a database of outside trainers. Each trainer can handle multiple categories of training. It is essential that the training manager be able to create lists for specific categories as the need arises, as well as being able to do more standard sorts such as Name. Rather than making multiple entries for each Trainer (one for each category, which may reach 10+), I'd like to allow multiple entries in the Category column utilizing a key for each category. It seems that AutoFilter would be the ideal method for creating this db. Well, actually Advanced AutoFilter, since...

Trouble Printing Checks #2
I'm using Money 2004. I have a list of checks to be printed, but no 'Print Checks' on the left side panel. When I go the 'File' menu to print checks it gives me a "You don't have any accounts with checks to be printed." message. What can I do? David See http://umpmfaq.info/faqdb.php?q=91. "David" wrote: > What can I do? I would run a file repair to see if that will correct the number do indicate the number of checks that have been set to "print". "David" <David@discussions.microsoft.com> wrote in message news...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

Thread Problems
Found this from MSDN->codeguru, can't seem to figure out the problem.... class ThreadClass { protected: HANDLE m_hThread; //Thread handle bool m_bActive; //activity indicator DWORD m_lpId; //Thread ID public: ThreadClass(){} virtual ~ThreadClass(){Kill();} //Thread Management bool CreateNewThread() { m_hThread = CreateThread(NULL, 0, (LPTHREAD_START_ROUTINE)_ThreadFunc, (LPVOID) this, 0, (LPDWORD) &m_lpId); if (m_hThread == NULL) return false; m_bActive = true; return true; }; //Wait for thread to end bool Wait() {return (WAIT_OBJECT_0 == WaitForSingleObject(m_hT...

View Only Admin
I am having problems with the view only admin delegation. I put the appropriate group into the local admin on each exchange box but after a couple of days exchange removes this group. What is causing this and how do fix it so it leaves this security group in there. Thanks for your help -- Justin This can happen due to conflicting policies or rules. Try to enable Auditing for the security log and enable loging for Exchange in system manger and check results. "Justin" wrote: > I am having problems with the view only admin delegation. I put the > appropriate group ...

can't delete mailbox #2
I deleted a user account in AD but when I go to Exchange system manager I can't delete the mailbox. What is the deal? Wait until AD replicates then you'll be able to purge the mailboxes with the red Xs "Brandon Baker" <brandon@discussions.microsoft.com> wrote in message news:%23fkHzmOMGHA.1180@TK2MSFTNGP09.phx.gbl... >I deleted a user account in AD but when I go to Exchange system manager I >can't delete the mailbox. What is the deal? > Right click on Mailboxes in ESM and select Run Cleanup Agent. -- John Oliver, Jr MCSE, MCT, CCNA Exchange M...

Product list in CRM v.1.2
Hi all, i have another question concerning the product list, can i attach an excle sheet that contains my products instead of loging each product one by one as i have over 25,000 products to log? -- Regards, Marwa I'm afraid not. This is a major pain point for a lot of people and I do not kno of any 3rd party add-ons that have addressed it either. -- Matt Parks MVP - Microsoft CRM "marwa" <marwa@discussions.microsoft.com> wrote in message news:C062CF1E-EEF8-43B4-AC31-F299BF707252@microsoft.com... Hi all, i have another question concerning the product list, can ...

Internal Mail Problem
Hi all, I want to restrict some users to send & receive mail from internal user only, so I try to remove their smtp address and leave the x.400 address in the properties. This works well in Exchange 5.5 environment. But, how can I do this in Exchange 2003 environment, I cannot removed the smtp address in Exchange 2003, Please help, thanks. On Sun, 7 Aug 2005 00:46:12 +0800, <a> wrote: >Hi all, I want to restrict some users to send & receive mail from internal >user only, so I try to remove their smtp address and leave the x.400 address >in the properties. ...

problems with windows live mail
when windows live mail starts to download from my gmail pop account I always a box pop up saying "Internet Security Warning" dialog box "The server you are connected to is using a security certificate that could not be verified. "The certificate's CN name does not match the passed value." "Do you want to continue using this server?" I click yes and it works, connects and downloads messages. Until the email client is close and restarted that is. Then once again on the first access the security certificate message is displayed. How do I get this to w...

OS version 10.2
In a previous post, I was told that Office 98 for MAC will not work on OS 10.2 and was directed toward getting Office 2004. In the system requirements for 2004, it says that it is for users who have 10.2.8 or later. Is OS 10.2 upgradable to 10.2.8 or do I need to go with Office X or 2001 for OS 10.2? Heath Kuszak IT Manager Verdone Landscape Architects, Inc. Heath Kuszak <heath@verdonelandarch.com> wrote: > In a previous post, I was told that Office 98 for MAC will not work on OS > 10.2 and was directed toward getting Office 2004. In the system requirements > for 2004, ...

Matrix #2
Hiya, I need to create a chart which is a matrix of four 'boxes' (a bit like the BCG 'Cash cow, star, etc matrix if you know it). But am having real trouble. What I need is this (and I'm going for the simplest method here - I ignored the four 'boxes' and just tried to get the data as I want on the chart): Data source: Project Name Value(1-10) Difficulty(1-10) Proj1 5 8 Proj2 8 10 Proj3 1 8 Proj4 3 ...

When will CRM 2.0 be released?
I was wondering if anyone has a firm(ish) idea when version 2 will be released. I've heard 2005, but that is a little vague. My company has identified a few gaps that should be resolved with the 2005 expected enhancements. If the release is put off until this time next year, we will go ahead and correct now. However, we would like to hold off wasting time\money is Microsoft will deliver within the 1st two quarters of 2005. Thoughts? The latest word is that CRM 2005 should be release sometime in calendar Q2 of 2005. Matt Parks MVP - Microsoft CRM ---------------------------...

Spinning Beach Ball
I seem to notice a correlation between seeing the spinning beach ball more often after launching MS Word for OSX. I removed the PDFMaker.Lib and this improved the performance a great deal, but still noticing the SBB more often after launching Word. Anyone know about this? Many Word issues are related to corrupt Word Settings or Normal Template. You could try removing the Word Settings file, then testing. The Normal Template could be renamed, then test. Be sure Word is closed before removing or changing any files. You could also consider running the Remove Office utility and reinstalling Offi...

Outlook attachment problems
Dear Our oversea office sender using Outlook Sending message (HTML) insert a picture sending message to my office, when I have received the attachment auto duplicate one set in the message. I have asked the sender try remove the picture and FORWARD to my e-mail, but the attachment sill in the message and auto duplicate one set attachment. Out overseas office using MS SBS , user setup WG mode for outlook download message. I am not sure which part problems or virus effect. Please advice how to solve out the problems ASAP. JACKIE ...

Problem Printing w/Calendar Assistant
Every time I attempt to print a calendar using the Assistant it prints one page and then stops. I have tried to print multiple pages of a weekly calendar and each time it prints one week and then stops. I love the layout and colors of the calendar but I can not get more than one weekly calendar to print no matter how many weeks I select to be printed. Calendar Printing Assistant is known to be quirky. It works best with Outlook 2007. I cant say i know a fix for your issue. However, if you cant work around for this issue or you need a better printing solution, consider using...

Interesting problem in Money 2005 and MSN link
Money 2005 Premium. I swore I'd never link to MSN on the web but yesterday somehow got there by trying to set up an MBNA card. I finally figured it wasn't worth it so I removed everything - passport and all data. Here is the interesting thing. Previously, my mny file was 46MB after a shrink. After removing everything, it shrank to 41MB. I didn't really care since I had a backup but I decided to explore further. In putting everything through MSN and then disabling it all, it reversed the sign on 2 transactions (one in 1998, the other in 2003) but nothing else was touched. All 22,...

Very strange problems while running Great Plains on workstations
I notice that a few workstations in an office I support are having problems when they run Great Plains. Excel, Outlook, Word and Dynamics.exe are showing in the Application event log as being Hanging or Faulting. I also see Fault Bucket errors, but when I search online I cannot find any information online. Here is one of the Fault Bucket errors: 3:15:35 pm 28-Sep-06 Application Hang None 1001 N/A Fault bucket 296734104. Also, these workstations are experiencing problems printing PDF files. Has anyone out there seen this behavior and if so, how can these problems be fixed? Thank you, ...

workaround for non normalized table
I've inherited a database that relies heavily on a non normalized table. Until I can convince the general manager that I can normalize the data without losing any records, I've got to have a workaround for certain situations. Namely, I need to ensure that data is not being badly reproduced at various stages of our operations. I would therefore like to reference the information directly from the main table in order to populate certain information in related tables. In this case, the main table uses [Order Number] as its primary key, and ties it to a bunch of information like ...

Excel #2
College text asks students how to save a file with a preview? I assume a print preview? Any suggestions? Choose File>Properties On the Summary tab, check 'Save preview picture. Click OK Tech Coor wrote: > College text asks students how to save a file with a preview? I assume a print preview? Any suggestions? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Help With Problem: Nested IF Function, Office2K v Office2003
Hello Group, I'm looking for some help with a nested IF worksheet function. Shown below is a formula that I'm using in a model. =SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="<90",worksheet!$C$3:$C$54,0),0)) If short, what I'm trying to do is scan the data in two columns and for any cells which meet the TRUE case in both columns, then sum the third numbers for all cases returning TRUE-TRUE. If either cell returns a FALSE, then enter 0. I created the original formulas and model in Office2000 and have used the model for more tha...