View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
J_J[_2_] J_J[_2_] is offline
external usenet poster
 
Posts: 140
Default How can we classify referring to grades?

Tushar ,
thanks for your efforts. Yes this worked well for me too but I need to
create a sort of file that may be used by anybody. The data region/size may
vary, the departments may vary and the point limits may vary.
Thus a programmatical approach is much suitable here...
Hope you can still propose an alternative
Regards
J_J



"Tushar Mehta" wrote in message
m...
You are probably better off doing this by hand with a little bit of
help from XL.

Suppose your original data are in B1:F13. Then, duplicate the data set
in B16:F28. Now, sort this 2nd data set on the score column
(descending). Draw a lower border for the person who matches the cut-
off scores for the individual departments. In the sample data set that
would be Martyn at 70 and Sue at 65.

In I15:K15 enter A, B, and C. In I16 enter the formula =COUNTIF($H$17:
$H$28,I15) Copy it to J16:K16. These three cells tell you how many
candidates have been assigned to each department.

In column H, starting with the first candidate (row 17) assign that
person to her/his first choice. Keep on going down the list as long as
the numbers in J16:K16 are less than the established capacity and the
candidate is above the first threshold (cell border drawn in para 2
above).

Once a department capacity is reached, start assigning people based on
their second (and later their third) choices. Once the department
minimum requirement is reached stop considering that department for all
candidates below that row.

For the provided sample set, I get:
Nancy A
Mac B
Judy A
Mag B
Karen C
John C
Judy C
Martyn A
Jon B
Bush B
Sue C
Mandy -

IMO this is not only very transparent but also the quickest and the
easiest to authenticate. Compared to it any programmatic solution (or
formula based solution) will take longer just because of the debugging
and testing requirements. In addition, it still may not be as
transparent as the above.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , says...
Hi,
I have a problem that I beleive can be solved via VBA programming...
Say we have candidates that have applied for entrance to a school.
An entrance examination is done and each candidate gets a total point as
a
result.
Say we have 3 departments. DeptA, DeptB and DeptC in the school.
Each dept has a limit of points in order to apply and a max total
capacity
of students.
On application Each candidate also supplies priorities for
himself/herself
for the depts he/she applied.
Thus say we have a excel table like this:

Name Points 1stChoice 2ndChoice 3rdChoice
Jon 67 A B C
Martyn 70 A C B
Sue 65 B A C
Karen 78 C B A
Bush 66 A B C
John 78 C A B
Mac 89 B A C
Nancy 90 A C B
Mandy 56 C A B
Judy 89 A C B
Mag 86 B C A

Judy 77 C B A

and Limit Points for DeptA, deptB and DeptC are 70, 55 and 65
respectively
and Capacity for Depts A, DeptB and DeptC are 3, 4, and 4

How can we code excel to distribute these students to the depts so that
the
above scenario will be satisfied?.
I will be happy glad if you can direct me to similar excel solutions to
such
problems or provide a code that I can work on.
TIA
J_J