1. ## Admission Lottery (stratified) (2003)

Here's what I need to do. We are mandated by law to hold an lottery for admission. It needs to be stratified (weighted) to maintain racial/gender balance to the sourrounding areas. Currently we do this by hand, Pulling a number, then checking ethnicity and sibilings, writing o paper until we reach that goal. If the goal cannot be reached using the process, then all remaining entrants are placed depending on the order they were pulled.
The remainder applicants are then placed on a waiting list.

I would like to create a spread sheet where all applicants can be randomly pulled based on the criteria, to make it weighted. HOw do I do that in excel?

2. ## Re: Admission Lottery (stratified) (2003)

The attached may give you are starting point. It is only based upon two criteria, Sex and Race. Others may have a better way to do this. It does not include any VBA. You can see the Defined names by going to Insert|Name to see how the names used in various formulas.

Basically it sets values for Males and Females,(column E) (1 and 2 respectively). It also sets values for the races (column G)(AFAM,CAC, HISP) (3,1,2 respectively). I would hide these columns as they are not needed for anything else.

Column H adds the points. Column J determines if they meet the point qualification. If so, a Y indicates acceptance. Column K develops the waiting list if they do not meet the qualification.

Hope this gives you a starting place. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
You state that remaining entrants are placed on the order they wer pulled. I'm not exactly sure what this means. How are you going to determine equal scores?

3. ## Re: Admission Lottery (stratified) (2003)

Thanks for the good start! I can also add another colum to do the rand() function to randomize the list. Then rank based off the criteria. I'll post my progress!

4. ## Re: Admission Lottery (stratified) (2003)

I think the philosphy behind this is flawed. I thought the goal was to choose regardless of gender, race, national origin, etc. This scheme is biased and may preferentially choose based on those criteria.

If for example you are going to fill 50 admissions and have 100 applicants. If 75 of the applicants are female and 25 are male, that means you must take all the males and only 25 of the 75 females to maintain a 50/50 male/female ratio from the surrounding areas. This to me seems unfair to the female applicants who only have 33.3% chance of being selected while the men have a 100% of being selected.

Steve

5. ## Re: Admission Lottery (stratified) (2003)

The law for charter schools in my state says the admission is by lottery and we must comply with all desegration orders. The desegration order in my area states that the schools must reflect the community in which they live in. So it is somewhat skewed towards race. That's why the lottery is stratified. We try as a school to have equal gender in each class, it doesn't always work that way. Because we are also required to place sibilings first if there are openings. Sometimes we just get lots of girls (or boys) applicants (or sibilings) in a particular class. So we end up with one class out of 16, with 13 girls and 3 boys. Try teaching that class of tweeners...

I wish it was pure straight forward lottery but it isn't.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•