Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
the best candidate gets its first choice
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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.715 / Virus Database: 471 - Release Date: 04.07.2004 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
the best candidate gets its first choice
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 Wilson" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.715 / Virus Database: 471 - Release Date: 04.07.2004 |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
the best candidate gets its first choice
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","C an'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 "Martyn Wilson" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.715 / Virus Database: 471 - Release Date: 04.07.2004 |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
the best candidate gets its first choice
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. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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","C an'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 "Martyn Wilson" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.715 / Virus Database: 471 - Release Date: 04.07.2004 |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
the best candidate gets its first choice
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 "Daniel.M" wrote in message ... 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. "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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","C an'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 "Martyn Wilson" wrote in message ... 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.0 5 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.715 / Virus Database: 471 - Release Date: 04.07.2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
web browser choice? | Excel Discussion (Misc queries) | |||
Function choice | Excel Worksheet Functions | |||
Qestion to judge whether the candidate has passed or failed ...... | New Users to Excel | |||
No Choice But To Post Here | Excel Discussion (Misc queries) | |||
Is IF the best choice | Excel Worksheet Functions |