Adding Up
Posted by Peter on November 21, 2001 6:36 AM
I've read a lot of the messages on this board and realised that I know very little about Excel after all. So I've every confidence that someone can help me with this and if so thanks in advance.
We have to submit marks for coursework in our GCSE course (taken at 16 yrs old here in England)
The marks fall into four categories called P, O, A and E.
At least one of these marks must come from each Science i.e. Biology, Physics, Chemistry so an example might be
P O A E
Biology experiment: 5 5 3 3
Chemistry expt. 7 3
Physics expt. 4 4 4
Biology Experiment (2) 6 3
My problem is to get Excel to add up the best selection of marks given the rules above. In this case I would choose P7 (from Chemistry), O6 (from Physics), A4 from Physics and then because I have not yet had a contribution from Biology I have to choose the E3 rather than the E4.
I can do this by eye easily enough but when there are a hundred students each with five or six pieces of coursework it is easy to make mistakes.
I know about using functions such as Max() and Sum() and I am beginning to be familiar with array formulae but I know nothing about VBA so a solution without it would be much appreciated. However, if a macro is the only way then I'll happily start learning about them.
This would save me loads of time so anything you could suggest would be great
Posted by Juan Pablo on November 21, 2001 8:37 AM
I don't understand the logic... care to explain a little more ?
Posted by Dan on November 21, 2001 10:06 PM
Help me out with how you figure this out. The last thing you said was that because you didn't have a contribution from Biology, you had to use it's value of 3 for the E category. Do you always go from left to right when assigning the highest value for each category? I'm asking because I'm trying to figure out why, in this case, you couldn't use a 3 in the A category from Biology and use a 4 in the E category from Physics instead? If it is always left to right, then that makes your problem much easier to resolve because it is more straight forward (less "fuzzy") logic. Let me know.
Posted by Peter on November 22, 2001 11:26 AM
Dan and others
Thanks for the time you've spent so far. My last message wasn't very clear because I had to dash off to teach the next lesson. Let me give those numbers again (and by the way if you think the logic is unclear don't expect any argument from me! - blame the English education authorities!)
The numbers in my example should read:
Bio Expt: (P)5 (O)5 (A)3 (E)3
Chem Expt: (P)7 (O)3 (A)- (E)- not every piece of coursework is awarded a mark for all four skills.
Phys Expt: (P)4 (O)- (A)4 (E)4
Phys Expt 2: (P)- (O)6 (A)3 (E)-
I would love to take (P)7 (O)6 (A)4 (E)4 for this student but I can't because that would not include any Biology mark, so I have to sacrifice either the (O)6 or the (E)4 from the Physics experiments in favour of the lower mark from Biology. No, I can't automatically add up the highest in each skill and yes, I'm afraid fuzzy logic is probably the name of the game!
Doing this automatically is way beyond me, and may be beyond Excel but if any of you find a solution or at least tell me that no such solution is possible then at least I can stop worrying about it and go back to teaching. Thanks again. Pete
Posted by Peter on November 22, 2001 11:36 AM
Re: I don't understand the logic... care to explain a little more ?
Juan Pablo - please see my follow up to Dan's reply. Thanks Peter
Posted by Juan Pablo on November 22, 2001 12:10 PM
Re: I don't understand the logic... care to explain a little more ?
The optimum solution for this case would be:
P(7), O(6), A(4) and E(3). You can put this as a transportation problem. To solve it, try this:
(I'm copying this straight from a solver example "Transportation problem", applying to your case)
In C1:F1 put {"P","O","A","E"}
In A2:A5 put {"Bio Expt";"Chem Expt";"Phys Expt";"Phys Expt 2"}
Now, in C2:F5 fill in 1 (One's).
In B2:B5 put the formulas =SUM(C2:F2) and fill down accordingly.
In C6:F6 put =SUM(C2:C5) and fill right.
In C8:F8 fill with 1's again (Only ONE letter allowed, that is, one resut for P, one for 0, one for A, and one for E).
In A10:A13 put {"Bio Expt";"Chem Expt";"Phys Expt";"Phys Expt 2"} this is just for identification.
In B10:B13 fil with 1's (One result per Subject)
In C10:G13 fill the results for each student. {5,5,3,3;7,3,0,0;4,0,4,4;0,6,3,0}
In C15:F15 put this formula =SUMPRODUCT(C2:C5,C10:C13) and fill right
In B15 put =SUM(C15:F15)
Now, go to Tools, Solver, finally. Object Cell is B15, value is MAXIMUM, changing cells C2:F5.
Constraints:
B2:B5<=B10:B13
C2:F5>=0
C6:F6>=C8:F8
Click SOLVE, and BINGO, you have the combinations that you need...
Hope that helps !
Juan Pablo
Posted by Peter on November 24, 2001 1:18 AM
Re: I don't understand the logic... care to explain a little more ?
The optimum solution for this case would be:
