[ms office - 2010] Excel formula help needed: COUNTIF x Grade Weighting

[JamesDM]

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?

[pcstru]

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.

[JamesDM]

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 :-)

[TLARWise]

How about:

 

=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

[JamesDM]

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