Pivot Table Count Issue

[KingdomGirl]

I'm using Excel 2003. I'm a high school guidance secretary. The
administrators are constantly asking for data relating to course failures.
From our school's student management program, I've exported a listing of all
course grades for the year and used the data to create a pivot table.

Right now, I'm only using row fields: StuName, GradeLVL, TermID, StoreCode,
Course, Grade

I've filtered the data to only show records with a TermID of '1901' &
'1900'. (Semester 1 Final or Year Long). I've also filtered the StoreCode to
only show records with a StoreCode of 'S1'. I've filtered to Grade data to
only show records with a grade < 75. (Course Failures)

My delima is that I need to show ONLY records for students with two or more
course failures. Currently, I'm sorting through the records and hiding the
rows for students with only one course failure. It seems like there should
be a way for Excel to calculate this data for me, but I don't know how in the
Pivot Table.

Can you help?

Thanks!

 

[Dave Peterson]

Maybe you could add another column "Courses Failed" to the raw data.

If stuname is in column A and grade is in column F, then a formula like this in
G2 (nice header in G1):

=sumproduct(--(A2:A999=a2),--(F2:F999<75))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=======
Then you can use this new field as a page field and hide the records that are
less than 2.

(or modify the formula in G2:
=sumproduct(--(A2:A999=a2),--(F2:F999<75))>1

This will return True if the count is more than one.

========
When you move to xl2007+, you'll be able to use an =countifs() formula that can
check more than one column.

 

[KingdomGirl]

Hi, Dave. Thank you so much for your help. I consider myself pretty
proficient in Excel, but the SUMPRODUCT formula is completely new to me. I'm
excited about the possibilities.

I'm not sure what I'm doing wrong, but I can't get the formula to return the
correct number. As an example: rows A2:A26 contain records for a single
student. (We'll call him John.) There are multiple rows for each student
because each row represents a course that student has taken. Column A
contains the student name. Column F contains the course grade. I entered
the SUMPRODUCT formula into Column G, as you suggested. The result returned
was '17', but that is not correct. Looking at John's grades, he actually has
failed 7 courses. (Obviously not our best student!)

I don't know if it makes a difference, but I feel I should mention that my
data represents each student's entire course history, so the file is quite
large - over 30,000 rows.

Do you have any idea what I'm doing wrong?

Thanks again for your EXCELLENT help!

Julie

 

[Dave Peterson]

What formula did you use? (Just to make sure you didn't change it too much
<vbg>.)

Are there scores where John showed 75, but where is actual score is 74.9 -- and
the cell is formatted to not show decimals (or just to skinny to show them)?

 

[Dave Peterson]

ps.

I should have suggested a formula that didn't change the range to inspect:

=sumproduct(--(A2:A999=a2),--(F2:F999<75))
should have been:
=sumproduct(--($A$2:$A$999=a2),--($F$2:$F$999<75))

Then dragging down won't break the formula.

 

[KingdomGirl]

Okay, I posted too quickly. Figured out my problem. Actually, there were
two. The first is that some courses did not have a grade, so the blank was
calculated as a zero. That's where the 17 came from. The second problem is
because there are multiple rows for every student, the number of failures
decreased as the formula was copied down.

As a solution, I deleted all rows without grades. I reconfigured my rows so
the Student Number is now in column A. Then, I sorted by Student Number in
ascending order and Course Failure in descending order. I inserted another
worksheet and inserted all student numbers into column A, with a separate row
for each student. I entered a VLOOKUP formula into column B to reference the
student number on the data sheet and return the number of course failures.

So now I'm left with a true count of course failures for each student, which
brings up another delima: In any given grading period, I will need to know
how many course failures a student had in the previous term. The count I
have now is for the number of course failures a student has had in his/her
entire high school career. What is the solution? Perhaps insert colums for
course failures by term, and use an IF formula? Something like:

=IF(AND(D2=1901,F2<75),1,0)

Then I could use the SUMPRODUCT formula to count the numer of failures.

What do you think? Am I making this too complicated?

Thanks for your help!

Julie

 

[Dave Peterson]

First, I posted a modification to my initial suggestion that fixed that range:

=sumproduct(--($A$2:$A$999=a2),--($F$2:$F$999<75))

You'll want to add one more criteria to this to make sure the value in column F
is a number:

=sumproduct(--($A$2:$A$999=a2),--($F$2:$F$999<75),--(isnumber($f$2:$f$999)))

(You shouldn't have to delete those rows with empty cells in column F.)

=======
As for the previous term, this formula seems ok to me -- maybe watch out for
those empty cells:
=IF(AND(D2=1901,F2<75,isnumber(f2)),1,0)

This is a shorthand version that will give the same results:
=--(and(D2=1901,F2<75,isnumber(f2)))

The =and() will return true/false. The first minus will change true to -1 (and
false to 0) and the second minus will change the -1 to +1.

But use the one you're comfortable with.

One thing that I would consider doing is to dedicate a cell to hold that
previous term.

Then you'll just have to change it one location (instead of all formulas):

=IF(AND(D2=x99,F2<75,isnumber(f2)),1,0)
or
=--(and(D2=x99,F2<75,isnumber(f2)))

I used X99, but you'll want it closer/easier to see.

And remember that if you have a Text value of '1901, then it's not equal to the
number 1901.