#### Pivot Table Help #3

```I have a lot of data that I am trying to analyze with a pivot table and am
not sure how to go about it.

Columns are
(1) District
(2) Store #
(3) 2003 Score - these are #s or text ("incomplete")
(4) 2004 Score - these are #s or text ("incomplete")

For each district, I am trying to find out 3 things:

(1) % of stores incomplete
(2) Average score for 2003 & 2004 - I've got this one working properly
(3) % change between 2003 & 2004

I can successfully analyze the data in a spreadsheet but there is too much
to go through and thought a pivot table was the way to go.

All I need is the totals, don't really care to show all of the data behind it.

Any suggestions on how to do this or where to go for a tutorial is very much
appreciated. Thank you all!
```
 0
LoriM (17)
2/1/2005 2:07:08 PM
excel.misc 78881 articles. 5 followers.

2 Replies
359 Views

Similar Articles

[PageSpeed] 32

```I think I'd insert a couple more columns in the data table.

then use a couple of formulas to indicate Incomplete.

=if(c2="incomplete",1,0)
=if(d2="incomplete",1,0)

Then you can add those columns to your pivottable as averages (formatted as
percentages).

Depending on the version of excel you're using, you can use an =getpivotdata()
formula.
(See Debra Dalgleish's site:
http://www.contextures.com/xlPivot06.html)

In point #3:  did you mean the difference in averages?

LoriM wrote:
>
> I have a lot of data that I am trying to analyze with a pivot table and am
> not sure how to go about it.
>
> Columns are
> (1) District
> (2) Store #
> (3) 2003 Score - these are #s or text ("incomplete")
> (4) 2004 Score - these are #s or text ("incomplete")
>
> For each district, I am trying to find out 3 things:
>
> (1) % of stores incomplete
> (2) Average score for 2003 & 2004 - I've got this one working properly
> (3) % change between 2003 & 2004
>
> I can successfully analyze the data in a spreadsheet but there is too much
> to go through and thought a pivot table was the way to go.
>
> All I need is the totals, don't really care to show all of the data behind it.
>
> Any suggestions on how to do this or where to go for a tutorial is very much
> appreciated. Thank you all!

--

Dave Peterson
```
 0
ec357201 (5290)
2/1/2005 11:22:18 PM
```Thanks for your response, Dave. I got it to work!
LoriM

"Dave Peterson" wrote:

> I think I'd insert a couple more columns in the data table.
>
> then use a couple of formulas to indicate Incomplete.
>
> =if(c2="incomplete",1,0)
> =if(d2="incomplete",1,0)
>
> Then you can add those columns to your pivottable as averages (formatted as
> percentages).
>
>
> Depending on the version of excel you're using, you can use an =getpivotdata()
> formula.
> (See Debra Dalgleish's site:
> http://www.contextures.com/xlPivot06.html)
>
> In point #3:  did you mean the difference in averages?
>
>
>
> LoriM wrote:
> >
> > I have a lot of data that I am trying to analyze with a pivot table and am
> > not sure how to go about it.
> >
> > Columns are
> > (1) District
> > (2) Store #
> > (3) 2003 Score - these are #s or text ("incomplete")
> > (4) 2004 Score - these are #s or text ("incomplete")
> >
> > For each district, I am trying to find out 3 things:
> >
> > (1) % of stores incomplete
> > (2) Average score for 2003 & 2004 - I've got this one working properly
> > (3) % change between 2003 & 2004
> >
> > I can successfully analyze the data in a spreadsheet but there is too much
> > to go through and thought a pivot table was the way to go.
> >
> > All I need is the totals, don't really care to show all of the data behind it.
> >
> > Any suggestions on how to do this or where to go for a tutorial is very much
> > appreciated. Thank you all!
>
> --
>
> Dave Peterson
>
```
 0
LoriM (17)
2/15/2005 4:43:19 PM

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

Form for table
Here's the problem: Access 2003, XP Pro- I have three tables that are related by primary keys. I have a table[Table QC] that has the field [EmployeeID], the data type is number--that field is linked to the autonumber primary key of the Employees Table. So far so good. I am trying to make a form that has a combo box that looks to the Employee Table to get me the drop down of the employees (Last Name, First Name). The SQL statement builds out okay, and I get the correct name config., but because the data type is a number it won't accept the data. I looked at the Northwind example and i...

Microsoft Outlook 2002 #3
How can I allow Level 1 files into Outlook 2002, I keep getting the following message "Outlook blocked access to the following potentially unsafe attachments" Thanks, Stephen Some options here http://www.slipstick.com/outlook/esecup/getexe.htm "spike" <stephen@just4u.ie> wrote in message news:090201c35c10\$19100be0\$a601280a@phx.gbl... > How can I allow Level 1 files into Outlook 2002, I keep > getting the following message "Outlook blocked access to > the following potentially unsafe attachments" > > Thanks, > > Stephen ...

I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

List Running Horizontally-Pivot Table Possible?
I just had a customer ask this one and I'm not so sure about it. 1) The customer has setup a list of information that is running horizontally instead of the typical list running vertically. 2) I just taught him how to do pivot tables and he wants to generate a pivot table with his "horizontal" list. 3) I know he can copy and paste special and use the Transpose feature to change the list from horizontally oriented to vertically oriented. 4) the question is: can he leave the table in its original orientation and still generate Pivot tables? I can't seem to make it work ...

how do i rotate a 3 shift 200man schedule
like 80 people on two shifts and 40 on the third. i want to move small shift to day and slide half of day into swing and half of swing into graveyard. any suggestions? thanks in advance. First rotation: pick the first 40 people (by alphabetic order) from the larger groups to rotate to the next shift. Each subsequent rotation: move the group of 40 that did not move the last time. -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <6F26D7D8-3A2F-4663-BE17-F0EF1AD26A3D@microsoft...

I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F\$9:G\$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...

cdrom.sys corrupt in Win7
Yesterday Win7 decided to no longer show my 2 LiteOn DVDRW drives. I've tried to re-install/repair the driver (6.1.7600.16385) and everytime I get the same response = my current driver is good. BUT, then when I check with Device Manager, it shows that the drives are not working. Can anyone help me get a new cdrom.sys installed into the system32/drivers folder? Booting up with the Win7 DVD will work. But I can't find the cdrom.sys on the disk. No other repair options are there to get this fixed. Help would certainly be appreciated. I don't want to have to start all o...

Exchange 2003 SMTP QUIT
= = = = = = = = = = = = = = = = = = = = = = = = = = = PROBLEM: Problem is that OUR SERVER is sending QUIT-, instead of sending MAIL FROM: MY Server open a SMTP connection REMOTE Server says 220 .. MY Server says EHLO to REMOTE Server REMOTE Server says 250 ... MY Server then say QUIT ! (instead of MAIL FROM ....) We have::Exchange 2003 , cu SP1, pe Windows 2003.. Exchange has also IMF (spam filter from Microsoft) and Symantec Mail Security for Exchange 4.5. all PTR is installed and working OK. The SMTP Server is workin OK a while, then it start opening a lot of connections (7-10 /sec) t...

Frequency #3
I'm using excel with phstat add-in. How do I graph so that the units of the variable are along the x-axis and just the count or frequency is along the y-axis? Secondly, how would I get excel to plot or i.d. the mean and the standard deviation units along the x-axis? Thanks for your help. wrace. Wrace - You might need to devote an additional section of your worksheet to condition the data for plotting. I don't know about phstat, so I don't know how extensively you will need to adjust your data. To put a vertical line where the mean and SD are located, check out this web pa...

CRM 3.0 Locks up Outlook
I have tried to re-install, tried repair nothing works. What is the deal with this software??????? Hi Mark, Do you own alot of contacts in the CRM system? If so, you first time starting the application after the installation will probably take a long time because it synchronize all your contacts to your Outlook. If you don't want the initial sync, you need to disabled the sync by going to your personal settings in CRM or the registry on your laptop. Darren Liu Crowe Chizek http://www.crowecrm.com On Jul 27, 2:36 pm, Mark <M...@discussions.microsoft.com> wrote: > I have tried ...

Pivot Table data values
I need make a pivot table using the values as the data. I used to be able to do this, but now it only sums, counts or calculates. Is there a way to return the data values? ...

I need help
Here is my problem. A couple of years ago I began playing around with money. But I have come to the conclusion that I really do like the services, but I now have a bunch of acct. info that should have been input in a better manner. I want to know how do I delete all of the old info so that I can start fresh. I deleted that money program once and reloaded it but it retrieved all of the old garbage that I did not want. Your help is greatly appreciated. Sincerly, Matt On Tue, 19 Aug 2003 20:58:14 -0700, "Matt" <mrisher4@hotmail.com> wrote: >Here is my problem. A c...

Help styles
With great difficulty I am now creating documents with some styles.and outline numbering Now when I copy a table from another document into this document I get 1.1.1.1 and similar numbering all over the table. How do I accomplish the copy without the numbering inside the table? afd Try this: Select the table cells and press Ctrl+Shift+N. This (re-)applies the Normal style to text. -- Stefan Blom Microsoft Word MVP "afdmello" <afdmello@hotmail.com> wrote in message news:%23R7DwkCdKHA.4952@TK2MSFTNGP06.phx.gbl... > With great difficulty I a...

Linking a table from a Secure database
I have 2 database both with their own workgroup file(mdw) I like to link a table from one secure database to the other secure database. Is this posible with them having two different workgroup(mdw) files. On Thu, 10 Dec 2009 12:30:01 -0800, cpager <cpager@discussions.microsoft.com> wrote: I don't think so. The more common scenario is where several apps are secured using the same MDW. After all, if Joe is in the ShippingDept group, he is so for all applications. -Tom. Microsoft Access MVP >I have 2 database both with their own workgroup file(mdw) I like to li...

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 1...

Very Old Version of MSMoney (3.0)
I'm currently using a version of MS Money from 1994 on Windows 98....works perfectly! I have a new computer using MS XP... When I try to run this version of Money I get a "general protection fault in module "msmoney.exe 0059.0504"". Money Standard 2002 came with the system but I can't find it on the recovery disk to re-install. Would rather not re-install complete system if possible. You might need to run the program in compatibility mode. Find the executable for the program and right click, selecting properties. Set the compatibility option for Windows 95 or 9...