Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Aggregating Coursework Marks (2K/XP)

    I need to aggregate a cohort's coursework marks following criteria laid down by the exam board. Steve kindly provided a solution I used this year, but since then, the rules have been changed. The attachment shows the layout and the criteria. Any takers?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregating Coursework Marks (2K/XP)

    This post and the next give the two files Steve provided. They are in two files because of file size rules.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  3. #3
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregating Coursework Marks (2K/XP)

    Here is Steve's second file. The original post from earlier in the year has been purged.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Aggregating Coursework Marks (2K/XP)

    You will have to refresh my memory and probably any other takers:
    What is general problem?
    What were the ORIGINAL criteria and what are the criteria now?

    My solution was based on looking at ALL possible solutions that met the criteria. If the criteria have become more lenient, you might run into problems with too many possibilities to do it this way. I had over 16,000 solutions meeting the criteria.

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Aggregating Coursework Marks (2K/XP)

    The present criteria are given in the first attachment. The original criteria were:
    4 separate pieces of work (now 2 minimum, 4 maximum)
    8 marks recorded (as now),
    Minimum of two subject areas (as now)
    Each skill area represented twice (as now).

    I hope this helps.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Aggregating Coursework Marks (2K/XP)

    I don't have your original request, but the criterion of "ONLY" 4 separate pieces was either NOT stated or I misunderstood. In my calcs I had ALWAYS used NO MORE than 4 (Allowing for using 2 or three pieces). You have to have at least 2 since you need 8 items. So if this criterion is the "Only" one changed, the calcs still work and LAST year you might have had some selections with only 2 or 3 (rare, you generally can get HIGHER scores by using MORE pieces).

    Here are some "bits" from my solution:
    Some background on the calcs. To pull the 8 out of the 24 allows for 735,471 possible permutations. Even with so many it can be calculated easily with one array formula (gets the top 8 of the 24):
    =SUM(LARGE(C4:Z4,ROW(INDIRECT(""1:8""))))"

    Getting only 2 from each skill level reduces the combinations about 15-fold to 50,625. Even this can be calculated with 1 mega-formula:
    =+LARGE(IF($C$3:$Z$3=""P"",C4:Z4),1)+LARGE(IF($C$3 :$Z$3=""P"",C4:Z4),2)+LARGE(IF($C$3:$Z$3=""O"",C4: Z4),1)+LARGE(IF($C$3:$Z$3=""O"",C4:Z4),2)+LARGE(IF ($C$3:$Z$3=""A"",C4:Z4),1)+LARGE(IF($C$3:$Z$3=""A" ",C4:Z4),2)+LARGE(IF($C$3:$Z$3=""E"",C4:Z4),1)+LAR GE(IF($C$3:$Z$3=""E"",C4:Z4),2)
    "

    Cutting out the combinations, for using 5 and 6 pieces of work reduces the number to 16,245 (6 has 8,460 possiblilities and 5 has 25,920). For those interested: 4 has 14,670; 3 has 1,560; and 2 has 15.

    The criterion for more than 1 subject reduces the total to 16,242 (there are 3 possibilities: one for each subject).

    The brute force method I used is make to make a matrix of the 16,242 possible patterns.Mulitply this "16k" rows x 24 Col matrix by the transpose of (1 row x 24 col) "results" <transpose yields a 24 row x 1 col matrix> to yield a "16k" rows x 1 col array of possible scores meeting all the criterion. Taking the max of this array, gets the max score and using MATCH gets the row of this pattern for later use.

    Steve

Posting Permissions

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