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