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