the best candidate gets its first choice

  • Thread starter Thread starter Martyn Wilson
  • Start date Start date
M

Martyn Wilson

Hi,
I know this may be a bit tough but here goes my problem anyway...
I had an establishment problem that I am looking for an excel solution.
Say we have 3 departments. DeptA, DeptB and DeptC in a school. To accept
students for the first classes each have various capacities. Say DeptA can
accept 24 students while DeptB can only accept 20 and DeptC 22. When
applying to the school, the candidates present 2 criteria + an exam result
to be used by the admin to classify them to the depts.
- Past school average (a number, max 10): say variable SA
- School entrance examination result (a score max 10): say variable ER
- Priority of dept for students (students need to give top priority and a
second choice): say variables P1 and P2

The formula to evaluate each student success sub total is:
SA*0.50 + ER*0.35

For the first choice Dept (P1) this sub total is to ve multipled by 0.10
For the second choice Dept (P2) this total is to be multipliesd by 0.05

Two marks will come out for each candiadate. Mark for the First Choice and a
mark for the Second Choice. Thus two lists for the Dept candidacy. One for
the first choice and one for the second.
All applicants are then sorted according to thouse marks. First they are
tried to fill in the candidacy needs of the depts according to the first
choices list. If not all depts are filled then the second choice lists are
taken into account students with most successfull totals gets the places.

How can I use excel macro's to solve this problem?
Thank in advance
Martyn
 
Hi Martyn

dont' need to use macros - i've got an exmple workbook i can send you if
you'ld like to email me direct

Cheers
JulieD
julied_ng at hcts dot net dot au
 
Martyn,

I do registration for an afterschool program, and below is how we do it.
Macros won't help, but smart formulas and filtering will.

Before I go on, though, I do want to suggest that, while unlikely, it is
possible with your scheme that one of the better applicants gets turned
down.

Let's say that 25 Albert Einstein look-alikes apply for Dept A (which has a
limit of 254), and each also applies for Dept B. But, while the 20 students
that apply for Dept B are pretty bright, none is Einstein-like. So, one
Einstein gets turned down from Dept A, but gets shut out of Dept B because
it is filled with students that are just a little better than half-as-smart
as Einstein.

That said, the steps below account for that.

Set up your data table: Enter labels in Row 1.
Enter the students names in column A, starting in A2.
Enter the first score in column B.
Enter the second score in column C.
Use a formula in column D: in cell D2, enter:
=B2*0.50 + C2*0.35
and copy down to match your list.
Columns E, F, and G correspond to DeptA, DeptB, and DeptC.
Enter "First" in the correct column for their first choice, and "Second" for
their second choice. Leave the third column blank.

In cell H2, use this formula: it's a long one, so you will need to take out
any extra line wraps: it should all be on one line:

=IF(AND(E2="First",COUNTIF($H$1:H1,"A")<24),"A",
IF(AND(F2="First",COUNTIF($H$1:H1,"B")<20),"B",
IF(AND(G2="First",COUNTIF($H$1:H1,"C")<22),"C",
IF(AND(E2="Second",COUNTIF($H$1:H1,"A")<24),"A",
IF(AND(F2="Second",COUNTIF($H$1:H1,"B")<20),"B",
IF(AND(G2="Second",COUNTIF($H$1:H1,"C")<22),"C","Can't Place"))))))

Fill that formula down to match your list, and when you go to print out your
class lists, simply filter based on column H.

HTH,
Bernie
MS Excel MVP
 
Interesting solution. Thanks Bernie.

It might be better to sort them descending on Column D or else their order of
appearance in the list will exclude the last one on the list from having his
first choice, even if he/she had the best results of the group.

It won't prevent one Einstein of being left off (25 for only 24 offers) but at
least, it will leave out the one with the worst result amongst those 25. :-)

Regards,

Daniel M.
 
Did I forget to say to sort descending based on column D? Aaah! Brain
freeze.... I edited that section right out.... without it, this doesn't
work.

Sorry about that.

Bernie
MS Excel MVP
 
Back
Top