JamesDM Posted August 20, 2012 Posted August 20, 2012 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 Posted August 20, 2012 Posted August 20, 2012 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. 1
JamesDM Posted August 20, 2012 Author Posted August 20, 2012 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 Posted August 20, 2012 Posted August 20, 2012 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 1
JamesDM Posted August 20, 2012 Author Posted August 20, 2012 That's the nice neat solution I was after, thanks.
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now