Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Count Issue
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Count Issue
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 he 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 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Count Issue
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" wrote: 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 he 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 wrote: 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 . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Count Issue
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)? KingdomGirl wrote: 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" wrote: 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 he 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 wrote: 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 . -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Count Issue
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 wrote: 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" wrote: 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 he 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 wrote: 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 . -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Count Issue
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" wrote: 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 he 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 wrote: 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 . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Count Issue
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. KingdomGirl wrote: 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" wrote: 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 he 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 wrote: 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 . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Issue with pivot table | Excel Discussion (Misc queries) | |||
One pivot table cannot overlap another pivot issue. | Excel Discussion (Misc queries) | |||
Pivot Table Issue | Excel Worksheet Functions | |||
Pivot table sum issue | Excel Discussion (Misc queries) | |||
Pivot Table Issue | Excel Worksheet Functions |