Pivot Table Design

[Bob]

Hello,

I am working on learning Pivot Tables and wanted some advice. I would like
to use the Pivot Tables to work with state testing data. Fields that I
would be working with would include:

Student Name
Student Grade Level
Race
Special Ed (Y or N)
English Language Learner (Y or N)
Free and Reduced Meals (Y or N)
Math Level (1, 2, or 3)
Math Score (a 3-digit raw score)
Reading Level (1, 2, or 3)
Reading Score (a 3-digit raw score)

I would like to be able to generate reports that would show the students in
a race that scored at a certain level, or students in a race, who are free
and reduced, and special ed who scored at a certain level, etc. I would
like to get counts of the kids in those groups, as well as their names. I
would also like to look for students who scored above a ### score.

In the past I have used filters, but I am thinking that Pivot tables would
be better.

Thoughts?
Would this be approrpiate for Pivot Tables?
Suggestions on the layout?
Any other suggestions?

Thanks.

 

[Bob Flanagan]

Bob, I would set up each of your fields as a column. I would add one
additional column, and name it "Number". In it I would just put a one (1)
in each cell. Then in your pivot table You can do a sum of Number, and set
restrictions on your fields in the page area. Only records meeting the
criteria of your restrictions would be displayed.

The biggest danger is not expanding your pivot tables data range after you
have added data. Or not refreshing your pivot tables after changing data.
Each table should be expanded/refreshed to insure they are reading the
latest data. I've seen too often users not doing so and assuming tha
refreshing one table refreshes all tables.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[shubha.bala]

Bob, I would set up each of your fields as a column. I would add one
additional column, and name it "Number". In it I would just put a one (1)
in each cell. Then in your pivot table You can do a sum of Number, and set
restrictions on your fields in the page area. Only records meeting the
criteria of your restrictions would be displayed.

The biggest danger is not expanding your pivot tables data range after you
have added data. Or not refreshing your pivot tables after changing data.
Each table should be expanded/refreshed to insure they are reading the
latest data. I've seen too often users not doing so and assuming tha
refreshing one table refreshes all tables.

Bob Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, cell 302-584-1771http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel












- Show quoted text -

So for this part:
I would like to be able to generate reports that would show the
students
I would do something where the rows data is first race, then free and
reduced, and then special ed. The column headings should have the
Reading level column. I'm trying to think of how to make the Math
level column show beside that but to be honest I don't know how...you
might have only 2 choices: Either you make one pivot table for
reading and one for math, or you make the columns Reading level, and
then Math level below it, which in effect gives you 9 possible columns
for all the combinations of reading level and math level (1, 1 - 1, 2
- 1, 3 - 2, 1 - etc). From there you can filter out all the math and
reading levels you don't care about, or filter as you view. Using
either approach, you would then make the Student Names your data. I
don't agree that you need a column with "1" in it. It will count all
Student Names, on the assumption that every single row has some
student name value in it.

Now in terms of showing the *names* of these students, I don't know
that that is possible in pivot tables. I don't know of a "list"
functionality, I think just count and other math equations.

Lastly, you asked about showing a count of everyone with a score
higher than X. I think I just struggled through a similar problem for
the past 3 days and it is impossible to do directly from the pivot
table. The calculated field seems like the most likely candidate, but
it will *first* sum all the scores meeting the criteria, and *then*
determine if the result is above X which won't help you. Your best
bet is to add a column to your spreadsheet itself which has a blank if
the score is below X, and a 1 if it is above X, and then use that in
another pivot table (or possibly the one described above) where that
column is the data.