Forum Discussion
+/- Student Grade Formula Question
Good afternoon,
I work in a secondary school and we use the following grades: 1-, 1, 1+, 2-, 2, 2+, etc. I need to calculate the number of students that got a 1- etc in each subject. Obviously the + and - are an issue. Is there any way I can do this please?
Thank you
9 Replies
This is intended as a dynamic range version of a crosstab count.
= LET( distinctName, SORT(UNIQUE(Name)), gradeList, TOROW(gradeValidation), countGrades, COUNTIFS(Name, distinctName, Grade, gradeList), crossTab, IF(countGrades, countGrades, ""), VSTACK( HSTACK("Count", gradeList), HSTACK(distinctName, crossTab) ) )As others have pointed out, you have to format the cells containing the grades as text. If (alternatively) your grades had been A, A+, A-, B+, B, B-, etc., you would have had no problem. But making them "numbers" when you're not actually dealing with them as numbers for calculation (you are using numbers, yes, for their ordinal values)...that's why the addition of text to a number "obviously" mixes types. So make them all text, and then COUNTIF works, as does the Pivot Table. See the attached.
IMHO, COUNTIFS() works great with mix of texts and numbers. It automatically considers as numbers texts which looks like as numbers.
Same for PivotTable if use data model. Cached PivotTable takes "1" and 1 as different values.
In attached.
You might create a pivot table based on the data.
Add the subject to the rows area, the grade to the columns area and the student name to the values area.
Alternatively, create formulas using the COUNTIFS function
- Thank you
I did try the countif but it doesn't like the + or - attached to the gradescolee365 I don't understand why you say countif doesn't like the +/-. It seemed to work perfectly in the example I show below:
EDIT: BTW you can see I'm using mixed text and numbers based on the left vs right justification and it works fine and even if I search text it finds the values (notice the 'number' 2 is left justified with the triangle indicating it is formatted as text instead of numeric.
