+ Post New Thread
Results 1 to 5 of 5
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. #1

    Join Date
    Aug 2012
    Location
    Brighton & Hove
    Posts
    5
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    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. #2


    Join Date
    May 2009
    Posts
    3,155
    Thank Post
    279
    Thanked 857 Times in 641 Posts
    Rep Power
    335
    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. #3

    Join Date
    Aug 2012
    Location
    Brighton & Hove
    Posts
    5
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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 :-)

  5. #4

    Join Date
    Feb 2009
    Posts
    149
    Thank Post
    0
    Thanked 36 Times in 35 Posts
    Rep Power
    18
    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

  6. Thanks to HilcrRWise from:

    JamesDM (20th August 2012)

  7. #5

    Join Date
    Aug 2012
    Location
    Brighton & Hove
    Posts
    5
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    That's the nice neat solution I was after, thanks.

SHARE:
+ Post New Thread

Similar Threads

  1. Excel formula help
    By timbo343 in forum Office Software
    Replies: 9
    Last Post: 4th May 2012, 03:08 PM
  2. Excel Formula Help
    By timbo343 in forum Office Software
    Replies: 3
    Last Post: 6th May 2010, 09:42 AM
  3. HELP - Excel Formula - HELP
    By princessbucki in forum Office Software
    Replies: 7
    Last Post: 19th March 2010, 04:19 PM
  4. excel formula help again please
    By RabbieBurns in forum Windows
    Replies: 2
    Last Post: 13th August 2008, 05:31 PM
  5. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 04:56 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •