Office Software Thread, Excel formula help needed: COUNTIF x Grade Weighting in Technical; Hello, I'm using COUNTIF to find the number of each grade for GCSE results but I want it to recognise ...
1. ## Excel formula help needed: COUNTIF x Grade Weighting

Hello, I'm using COUNTIF to find the number of each grade for GCSE results but I want it to recognise that some or worth more or less than one GCSE. I was thinking of having a row with the 'GCSE Weighting' in it, i.e. 0.5, 1, 2 etc. but I can't think how to incorporate this into the fomula. I want it to look at the correct cell in the 'GCSE Weighting' row for that result, i.e. Count how many A's there and times it by the correct weighting for each occurance.

In previous years I've just grouped different weightings together and used seperate formulas for each group, but I'm trying to cut down the size of the spread sheet (and feel a bit clever).

Any ideas?

2. I find it's useful to have an intermediate sheet which will translate grades into (arbitary) numbers. This is usually just a straight reflection of a sheet with rows of name, followed by the grade achieved for each exam except, instead of grades I use a vlookup to get a number which represents the grade. It's then easy to create yet another sheet which can present the same grid but this time present just a 1 or 0 depending on the student achieving a pass. Now, if you have a row with the weighting, it's easy to combine that with the pass and get the number of GCSE equivilents. This does involve a number of very similar sheets but makes it very easy to then get the final statistics by student or whatever other criteria you are interested in.

3. ## Thanks to pcstru from:

JamesDM (20th August 2012)

4. I like your thinking pcstru, thanks. Think I'll create a second sheet, but use actual GCSE points instead of arbitary numbers and then I can use this for calculating point scores at the same time :-)

=SUMIF(\$E3:\$BB3,"A",\$E\$2:\$BB\$2)

where row 3 indicates the pupils grades
Columns E to BB are the different subjects
row 2 are the gcse weightings for the appropriate subjects

ie:
Eng,Mat,Sci...
2,1,3...
A,A,A...

formula should come out as 6 A's

6. ## Thanks to HilcrRWise from:

JamesDM (20th August 2012)

7. That's the nice neat solution I was after, thanks.

SHARE: