+ Post New Thread
Results 1 to 4 of 4
Office Software Thread, Excel formula for Capped Point Score/Best 8 per pupil in Technical; Has anyone got a solution for calculating capped point score per pupil in Excel? So far I've got SUM(LARGE(L2:BF2,{1,2,3,4,5,6,7,8})) but ...
  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 for Capped Point Score/Best 8 per pupil

    Has anyone got a solution for calculating capped point score per pupil in Excel? So far I've got

    SUM(LARGE(L2:BF2,{1,2,3,4,5,6,7,8}))

    but this doesn't take into account the GCSE weighting for equivalencies e.g. if a BTEC subject is in the eighth highest add this twice.

    I already have a row with GCSE weighting per subject on it so I almost need to combine the above formula with SUMPRODUCT...somehow?

  2. #2

    Join Date
    Oct 2010
    Posts
    23
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Blog Entries
    2
    Rep Power
    42
    I think your formula could be rewritten as:

    Code:
    =SUMPRODUCT(L2:BF2*--((L2:BF2>=LARGE(L2:BF2,8))))
    Then you could simply add in the weighting as follows:

    Code:
    =SUMPRODUCT(L2:BF2*$BG2:$DA2*--((L2:BF2>=LARGE(L2:BF2,8))))
    where $BG2:$DA2 contain your weightings for each subject column (except this might need to be an absolute range reference depending upon how you've set up your spreadsheet).

    HTH.

  3. #3

    Join Date
    Aug 2012
    Location
    Brighton & Hove
    Posts
    5
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Thanks for the reply but I can't get this to work, using the formula above gives me a value error. I tried using , instead of * to seperate the arrays in the formula but this just gives a total SUMPRODUCT, not a largest 8 SumProduct?

  4. #4

    Join Date
    Oct 2010
    Posts
    23
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Blog Entries
    2
    Rep Power
    42
    Make sure that all your arrays are the same size. That's the most likely source of the value error.

    Any chance you could paste the formula that was generating the error or a mock up of what your spreadsheet looks like?
    Last edited by Pico; 23rd November 2012 at 02:23 PM. Reason: Corrected myself.

SHARE:
+ Post New Thread

Similar Threads

  1. Excel Formula Copy Problem
    By sqdge in forum Office Software
    Replies: 26
    Last Post: 10th February 2013, 06:18 AM
  2. Average Points Score and Best 8 Capped
    By park_bench in forum MIS Systems
    Replies: 11
    Last Post: 5th December 2012, 02:30 PM
  3. office for mac excel formula question.
    By russdev in forum Office Software
    Replies: 2
    Last Post: 9th May 2008, 11:51 AM
  4. Excel Formula Help
    By DSapseid in forum Windows
    Replies: 4
    Last Post: 7th November 2007, 04:56 PM
  5. Time formula for hours and minutes
    By timbo343 in forum How do you do....it?
    Replies: 5
    Last Post: 1st October 2007, 05:55 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
  •