+ Post New Thread
Results 1 to 8 of 8
Office Software Thread, HELP - Excel Formula - HELP in Technical; ...
  1. #1
    princessbucki's Avatar
    Join Date
    Jun 2009
    Location
    Newton Abbot
    Posts
    11
    Thank Post
    9
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    HELP - Excel Formula - HELP

    A colleague is working on an Excel spreadsheet for Target GCSE grades, she has formulas for most things, however, we really need to see if there is a formula for the time taking bit!

    Each Grade in columns C through to Z are worth a certain amount of points, (A1=58; A= 52; B=46; C=40; D=34;E=28;F=22;G=16 which is the GCSE point scoring system) column AG is the total scored for all the GCSEs taken – for example Student 2 scored 372, he took 9 GCSE, but we want the score for the top 8 grades, so we subtract the lowest GCSE score which is ‘D’ worth 34 points this is shown in column AH. Total Points score subtract AF-2 should equal Total Points Score (Best 8)

    *Could a formula be created to work out the score of the bottom GCSE grades, ie: if they took 10 GCSE’s you would want a formula to subtract the lowest 2 grade scores and insert the result in column AH.

    *Could a formula be created to subtract the AF-2 from the Total Points Scored?

    I’m afraid I’ve never had to work out such complicated formula’s but if someone can help that would be wonderful

    Oh and if you don't read this until even months later and you think you can help, please reply, because it would just be wonderful to have the formula for future GCSE Gradesheets.

    Thank you
    Kirsty
    Attached Files Attached Files

  2. #2

    Join Date
    Oct 2008
    Location
    Manchester
    Posts
    64
    Thank Post
    1
    Thanked 13 Times in 13 Posts
    Rep Power
    15

  3. Thanks to pavlovs_dog from:

    princessbucki (19th March 2010)

  4. #3

    Join Date
    Jul 2009
    Posts
    24
    Thank Post
    1
    Thanked 4 Times in 4 Posts
    Rep Power
    11
    Are you trying to work out the capped point score? If so, I use

    =SUM(LARGE('Range',1), LARGE('Range',2), LARGE('Range',3), LARGE('Range',4),LARGE('Range',5), LARGE('Range',6), LARGE('Range',7), LARGE('Range',8))

  5. Thanks to donalouise from:

    princessbucki (19th March 2010)

  6. #4

    Join Date
    Jul 2009
    Posts
    24
    Thank Post
    1
    Thanked 4 Times in 4 Posts
    Rep Power
    11
    Forgot to say that I have a seperate worksheet that references the original exam results worksheet and converts the grades in points. The formula to get the 8 highest grade then references this spreadsheet. I then hide the tab for this worksheet so that it's a bit tidier.

  7. #5
    princessbucki's Avatar
    Join Date
    Jun 2009
    Location
    Newton Abbot
    Posts
    11
    Thank Post
    9
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by donalouise View Post
    Forgot to say that I have a seperate worksheet that references the original exam results worksheet and converts the grades in points. The formula to get the 8 highest grade then references this spreadsheet. I then hide the tab for this worksheet so that it's a bit tidier.
    Thank you, Im guessing this is probably what my colleague needs to do, I'm sure this is going to help no end.

  8. #6
    penfold_99's Avatar
    Join Date
    Feb 2008
    Location
    East Sussex
    Posts
    1,003
    Thank Post
    60
    Thanked 167 Times in 119 Posts
    Rep Power
    69
    If you a sims user this is possible in assesment managet then your not having to kepp exprting data.

    You can use the MAX function and then state 1 to 8 so you can generate the capped scorce.

    This also allows you to create a capped 8 from your current attainment

  9. Thanks to penfold_99 from:

    princessbucki (19th March 2010)

  10. #7

    Join Date
    Jan 2007
    Location
    Birmingham
    Posts
    807
    Thank Post
    29
    Thanked 36 Times in 24 Posts
    Rep Power
    27
    {=sum(large(c2:z2,{1,2,3,4,5,6,7,8}))}

    you will to press ctrl-shift enter to get the surrounding { } 's

    Dont forget that you now get bonus points for english and maths so it's your top 8 (which may include English and Maths) + additional bonus points for your English and Maths. so points across 10 subjects.

    Also dont forget that any subjects worth more than 1 GCSE/equivalent needs to be split. i.e. somebody acheiving a double distinction would need to have it counted as 2 grades worth 52 and not 1 grade worth 104.

    Lee

  11. #8

    Join Date
    Jan 2007
    Location
    Birmingham
    Posts
    807
    Thank Post
    29
    Thanked 36 Times in 24 Posts
    Rep Power
    27
    Quote Originally Posted by Lee_K_81 View Post
    {=sum(large(c2:z2,{1,2,3,4,5,6,7,8}))}

    you will to press ctrl-shift enter to get the surrounding { } 's

    Dont forget that you now get bonus points for english and maths so it's your top 8 (which may include English and Maths) + additional bonus points for your English and Maths. so points across 10 subjects.

    Also dont forget that any subjects worth more than 1 GCSE/equivalent needs to be split. i.e. somebody acheiving a double distinction would need to have it counted as 2 grades worth 52 and not 1 grade worth 104.

    Lee
    And a further one: if you have pupils doing early entry A-Levels i believe this can also be counted but for CVA purposes can only be counted as 1 grade dispite being a KS5 qual.



SHARE:
+ Post New Thread

Similar Threads

  1. Excel Formula
    By Pumaedition in forum How do you do....it?
    Replies: 6
    Last Post: 8th July 2009, 09:52 AM
  2. [MS Office - 2003] Excel formula?
    By leco in forum Office Software
    Replies: 3
    Last Post: 3rd July 2009, 11:43 AM
  3. Excel Formula
    By denon101 in forum How do you do....it?
    Replies: 3
    Last Post: 1st December 2008, 04:34 PM
  4. excel formula help again please
    By RabbieBurns in forum Windows
    Replies: 2
    Last Post: 13th August 2008, 06:31 PM
  5. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 05:56 PM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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