Aggregate Scores (97/2K)

Topic

New Reply

One of my tasks at school is to produce an aggregate total of coursework marks for the exam board. In previous years, the aggregation criteria have been straightforward and I have been able to automate this task using VBA. This has saved a lot of time since there are normally about 350 sets of students’ marks to record and calculate. This year, the exam board has changed the criteria and the task is now more complicated.

Each student produces 6 pieces of work, 2 each from the subject areas Biology, Chemistry and Physics. Each piece of work is assessed on 4 skill areas P,O,A and E. The aggregation criteria are as follows:

1. A minimum of 2 subject areas are represented.
2.A maximum of 4 pieces of work are selected.
3.Each skill area is to represented twice.

After aggregation, the 8 best marks (2 for each P,O,A and E) are recorded for the year group.

I have attached a sample worksheet showing the results for one student.

I know I can pick out the best aggregate scores by visual inspection so therefore it can be automated but I have not succeeded in so doing. With a year group in excess of 350, a manual solution would be very time consuming.
It may be that this is a fairly common programming task (or maybe not) but I would be unbelievably grateful for any ideas or assistance.

Cheers

Rob

Reply | Quote

Replies

Rob,

If you have time, could you please explain how you would determine the aggregate score for this illustrious student by visual inspection? TIA

Reply | Quote

Hans

Thanks for taking a look. I’ve tried to be as explicit as possible in the attachment which I hope is clearer.
I need to collect a total of the best two aggregate scores based on the criteria, i.e. collect 8 marks, 2 from each subject area.

I’d be grateful for any suggestions. Would Access be a better option?

Cheers

Rob

Reply | Quote

I think I understand how you arrive at the solutions.
My BIGGEST comment is that the question is NOT really an EXCEL question, but a LOGIC question. There a many possible solutions and many of the solutions will give identical aggregates.

TO understand the Logic, here are some additional questions to help clarify:
Is the total Agg score (P+O+A+E) important and if you have identical totals does it matter what the individuals skill areas are? Is an 8,7,7,6 equiv to a 6,7,7,8 or even a 7,7,7,7? (Are the ind skill areas important?)

Is the TOTAL of Ag1 and Ag2 significant or is individual total better? That is do you try for a stronger COMBINED or should you sacrifice Ag2 for a higher Ag1?

Also the criterion for the “8 selections”, when you calculate the agg1 and agg2 must they meet the same criterion individually (eg 2 subj areas) or is the criterion listed ONLY for the “8”. To put it another way, do you actually require the Agg 1 to be the BEST “4” meeting the criteria (1 subject area, each skill 1 time) and then Agg2 to the NEXT best “4” (ignoring the first 4) which meet the criterion (1 subject area, each skill 1 time).

In the above case, you are really solving 2 puzzles, not ONE (though the same logic in both) and the results might NOT be the same as just drawing the Best “8” meeting the criteria (1 subject area, each skill 2 times), then taking the max of each skill for Agg1 and the others for Agg 2. Both problems will have different logic.

I would think there would be some cases that 4 of the best 8 could be higher than the best “4”, since the “Best 8” would require >1 subject, but the choice of 4 of these “8” would not require > 1 subject.

For example all scores of 4 except to 1 work that got all 8s in Physics and 1 that got all 7s in Chem. The best 8 are those 2 exceptions which meet all the criteria. If you draw from these “8” Agg1 is all 8s (no requirement for 2 subjects) and Agg 2 is all 7s.

BUT if the indivisual Ags also require >1 subject, Both Agg1 and Agg2 would be 2 7s and 2 8s.

My gut feeling is, IF you could tell me the logic/algorithm to solve it (the LOGIC part) I (and many others on these postings) could “translate” into some code to calc (the EXCEL part).

But getting the LOGIC part is a real challenge. You almost will require trying every combination. and looking for the max. And some of the logic will also depend on your answers to the above. (and any more I think of later)

Steve

Reply | Quote

Hi Steve,

My second post to Hans was being prepared as you made your post.
Essentially, 7 of the 8 marks could come from the one subject area and 1 from any other. The combined total of the 8 marks is the important number. The maximum for each skill area is as follows:

P – 8
O – 8
A – 8
E – 6

Hence the total mark recorded is out of 60. It doesn’t matter where the marks come from as long as the criteria are met.

Does this clarify the problem in conjunction with the aforementioned post and the attachment?

Thanks for your help.

Cheers

Rob

Reply | Quote

(Edited by HansV on 31-Jan-03 08:50. Woops – I messed up the attachment at the last moment. It is correct now, I hope.)

Rob,

I created the attached workbook before reading this, so it includes a way to find not only the maximum aggregated score, but also displays one of the possible solutions. I turns out that you don’t need the latter.

My solution doesn’t use VBA code; instead, it uses array formulas and an auxiliary table. The idea behind it is to examine all 15 combinations of exactly 4 pieces of work; sometimes not all 4 are needed to obtain the maximum score, but that doesn’t matter. These combinations are filtered out using binary arrays in the auxiliary table; the LARGE function is used to find the two highest values for each skill.

Reply | Quote

Hans,
I’m impressed with your method and will have to study it a little.

It is a little flawed (I have yet to come up with a solution of my own, so you have beat me in round 1) but it does NOT ensure that ALL criteria are met. If someone has all 8s in Chem1 and all 7s in Chem2 and everything else are 1s, it will pick the 7s and 8s as best even though they ALL are in Chem.

The criteria seem to want to get “more rounded” aggs, so that a chemical “idiot savant” who knows NOTHING about physics or biology is NOT the best score.

Your method (if I understand it) will give you the MAX 2 from each skill area using at most 4 works. This meets crits 2 and 3 but NOT 1 (More than 1 subject).

My chemical “idiot savant” should NOT have a “60” (8*4+7*4) but should have “36” (8*4+1*4).

Steve

Reply | Quote

Steve, you’re quite right that my solution doesn’t meet condition 1. I will work on it, but if you find something in the meantime, you’re very welcome.

If I understand Rob correctly, it is sufficient to have one score from a second subject, so your chemistry buff would get an aggregated score of 8*4+7*3+1*1=54
(7 scores from chemistry, and only 1 from physics or biology).

Reply | Quote

You are correct, I guess that does meet the criteria, I will have to keep that in mind.
Steve

Reply | Quote

Hans,

You understand me perfectly.

Rob

Reply | Quote

The attached file is only PARTIAL. I have attached 2 CSV files to be opened and then added to the “Soln Matrix” worksheet. The files overlap rows so make sure you COPY into the right cell. The numbers in col A are continuous.

The file is large since I took the “brute-force method” to solve. I will go into that later.

The file has 3 sheets: “Scores” which is essentially like you had (I added some more examples) and calcs to get the aggregates and total score. I also have calculated the “best score” taking 2 from each skill, as well as the MAX Score from the best 8 from all 24. There is also a column for the “Row” which tells where in the “Soln Matrix” the solution is. [Note: most Totals are not UNIQUE, and this is ONLY one of any possible solutions to the problem.

There is also a sheet for “verification”. This has a combobox to choose a student. The sheet will “extract” the info from the “scores” sheet and put it here. It also grabs the “solution pattern” and arranges it in a matrix to show which values were selected. There is also a summary of how many subjects, pieces of work were used as well as the totals.

This sheet could be modified to get a “printout” for documentation if desired. A macro could be created to go through the list of students one-by-one and print the “pattern’ and those selected, if desired.

The last sheet is the “solution matrix”. The idea for this came after after viewing HansV’s approach. It uses a different “table” and calcs but the idea of using the matirx seemed good. It works though it is calc-intensive.

I gave up after some thought doing the Logic approach. I could solve them manually, but determining an alogorithm to do it proved difficult with all the criteria (hence, the reason for Rob’s original request). I decided to go for an excel approach! (Brute-force calcs)

“Just 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)+LARGE(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”
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.

The solution is complicated by the fact that this “16k” x 24 matrix proved to be too large to calc. I had to break it into 3 parts, get the max for each part, and then get the max of each of the 3 “part-maxes”.

The aggregates use the chosen pattern to determine the max and min for those chosen for each skill to be used for Aggregate 1 and 2.

Good Luck,
Steve

Reply | Quote

(Edited by sdckapr on 03-Feb-03 06:47. I should point out (I forgot to earlier). Before attempting to copy this to the other workbook. Set the calculations to MANUAL. Otherwise you will be waiting alot for excel to keep redoing all the calcs again and again as it adds each value to the table. After both are added it is fast enough that calcs can be automatic.)

Here part 1 of the csv to be opened and copied onto the Soln Matrix sheet.

Reply | Quote

Here is part 2 of the csv to be opened and copied onto the Soln Matrix sheet.

Reply | Quote

Hans,

I cannot thank you enough for the time and effort you put into this solution. I do need to record a possible solution because I need to identify which pieces of work were used in the aggregates in case they are required as part of a random sample to be sent to the exam board. There are a couple of questions puzzling me:

Would the auxiliary table be placed at the bottom of the worksheet beneath the circa 350 records?

When I change data in the subject marks, the totals change but the values and selections in the coloured range to the right of the aux table do not.

And of course there is Steve’s point about Criterion 1.

I am really excited as to the final outcome from which I would learn so much.

Again, many thanks to both you and Steve for taking an interest.

Cheers

Rob

Reply | Quote

Rob,

As Steve and you pointed out, I will need to work on Criterion 1. I will try to look into it during the weekend; probably Steve will come up with a far better solution in the meantime.

The aux table can be anywhere, even in a different worksheet (if you cut and paste it, Excel will modify the formulas automatically).

The coloured range to the right of the aux table is static, it contains no formulas or conditional formatting. I just used it to illustrate the results for the 3 students.

Reply | Quote

Rob,

Here is a new attempt. It is a lot simpler than my first one; it doesn’t use an auxiliary table. I hope that this does what you want.

Regards,
Hans

Reply | Quote

Hans,
Some comments:
1)Yours solution is MUCH shorter than mine…
2) Yours doesn’t filter out to ensure that 5 or 6 pieces of work are not selected. Your spreadsheet calcs this series: (for example)
8,3,3,3 3,8,3,3 7,3,3,3 3,7,3,3 3,3,8,8 3,3,7,7
As an aggregate of 4×8 and 4×7 = 60 (which is incorrect)
The best meeting ALL the criteria is only 52 (=4×8 + 2×7 + 2×3)
3) My last name is “APRAHAMIAN” and my PHYSICS scores should be much higher than my BIO scores

Steve

Reply | Quote

Steve,

I corrected your last name (sorry about that) and did something about yours physics scores in the attachment to my previous reply in this thread.

While trying to implement the “Atl least subjects” condition, I completely forgot about the “At most four pieces of work” condition, so I didn’t solve anything at all, really. I’m beginning to wonder if there *is* a (relatively) simple solution. What were the people in the Examination Board thinking when they designed these rules?

Reply | Quote

Hans,
I came to the conclusion, in my attempts to “solve it with Logic” that at some point, ANY algorithm that was used would have to be able to look at a variety of contingencies. I know it might NOT need all 16,242 variants (i didn’t use the fact that the numbers are between 1 and 8 so that limits the possibilities.

The easiest approach I decided was to just look at all 16,242 variants and pick the max. No logic, just brute calcuations. Sometimes you have to let the computer do the work!

Steve

Reply | Quote

Many thanks for the enormous time and effort that both you and Hans have put into this problem. The solution works beautifully. I will attempt to produce a summary sheet which presents the student name and aggregates colour-coded to show subject areas. If this proves too difficult for my meagre skills, may I get back to you?
Also, are you willing for me to share your solution with others to save them valuable time.

Again, I cannot express both my gratitude to and admiration for you and Hans.

Reply | Quote

Share to anyone. As far as I am concerned, If I post it online, it is free.
Just be careful what you delete and move as some items are important to calcs!

Post any followup questions to wopr.
Steve

Reply | Quote

Hans,

After some thought. I have had a few ideas.
Taking the 2 maxima from each column (P,O,A,E) would satisfy the criteria in most cases since it would be likely that at least 1 score would come from a different Subject area. Thus, having selected these 8 scores, they need to be checked for 2 conditions:

1)All scores are not from the same subject area.
2)The scores come from less than 5 of the 6 pieces of work.

If the first condition is not met, then, find either an identical score from the same column that is in another Subject area or, the highest number remaining from any column.

If the second condition is not met, then, find an identical number from a Subject area (row) already used or the next highest number from a Subject area already used.

I hope this all makes sense. BTW, the exam board has stated that it has changed the aggregation criteria to “simplify” the process!

Cheers

Rob

Reply | Quote