Results 1 to 6 of 6

20030922, 18:37 #1
 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>

20030922, 18:40 #2
 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>

20030922, 18:42 #3
 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>

20030922, 19:43 #4
 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

20030922, 20:08 #5
 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>

20030922, 23:44 #6
 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 15fold to 50,625. Even this can be calculated with 1 megaformula:
=+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