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!

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

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