Even Distribution of Students now with a Weighted Twist
 

Good afternoon, I have a problem that I have been trying to figure out
for a while. Let me first start by saying that I am not the smartest
person in the world I learn everyday. We have a poplulation of 1563
students. Are program will only allow 1110 students. We have 3 tests we
work from. Test X has 146 students, test Y has 723 and test Z has 694.
Since we have only 1110 openings what I did was to divide the
1110/1563. This would give me a percentage of about 71.0172744721689%.
Then I took the figure and I divided each test group
146/71.0172744721689%, 723 /71.0172744721689% and the
694/71.0172744721689%. When rounded this gives me 104 children for test
X, 513 for test Y and 493 for text Z. My question is, is there a way to
weight the different tests like test X =35%, test Y = 25% and test Z =
40% and have that come out to the 1110 students?

Thank you in advance
PJ

Even Distribution of Students now with a Weighted Twist
 

Even Distribution of Students now with a Weighted Twist
 

I think you multiplied, not divided, by 71%. For example, 146*71% ~=
104, but 146/71% ~= 206.
You are right I meant to multiply.

My question is, is there a way to
weight the different tests like test X =35%, test Y = 25% and test Z =
40% and have that come out to the 1110 students?
I understand your approach except for test X we only have 146 students
your example yeilds 389 students which is way to much for the test. Do
we have another alternitive method?

wrote:
wrote:
Let me first start by saying that I am not the smartest
person in the world I learn everyday.

Actually, it is the smartest people that "learn everyday" ;-).

We have a poplulation of 1563 students.
Are program will only allow 1110 students. We have 3 tests we
work from. Test X has 146 students, test Y has 723 and test Z has 694.
Since we have only 1110 openings what I did was to divide the
1110/1563. This would give me a percentage of about 71.0172744721689%.
Then I took the figure and I divided each test group
146/71.0172744721689%, 723 /71.0172744721689% and the
694/71.0172744721689%. When rounded this gives me 104 children for test
X, 513 for test Y and 493 for text Z.

I think you multiplied, not divided, by 71%. For example, 146*71% ~=
104, but 146/71% ~= 206.

My question is, is there a way to
weight the different tests like test X =35%, test Y = 25% and test Z =
40% and have that come out to the 1110 students?

1110*35% (about 389) should come from test X. 1110*25% (about 278)
should come from test Y. 1110*40% (444) should come from test Z.

Note that that sums to 1111, not 1110. One way to ensure the proper
sum is to compute the last category not by 1110*40%, but effectively by
1110 - 389 - 278. It might also help to use so-called "banker's
rounding" (round xxx.50...0 to the nearest even number). But that does
not obviate the need to compute the last category in the aforementioned
manner in the general case -- albeit sufficient in this particular case
by coincidence.

Even Distribution of Students now with a Weighted Twist
 

So you are saying that you want to apply some additional sort of weighting
in addition to the weighting by the number of students who took that test?

If you've got your number of students for the 3 tests in A1:A3, and your
weightings 35%,25%,40% in B1:B3, then you can produce a weighted number with
C1=A1*B1 and similarly down to C3. Your number of successful students from
test X could now become
=1110*C1/SUM(C$1:C$3) and you can copy that down to C2 & C3 (and deal with
rounding appropriately).
--
David Biddulph

wrote in message
ups.com...
I think you multiplied, not divided, by 71%. For example, 146*71% ~=
104, but 146/71% ~= 206.
You are right I meant to multiply.

My question is, is there a way to
weight the different tests like test X =35%, test Y = 25% and test Z =
40% and have that come out to the 1110 students?
I understand your approach except for test X we only have 146 students
your example yeilds 389 students which is way to much for the test. Do
we have another alternitive method?

wrote:
wrote:
Let me first start by saying that I am not the smartest
person in the world I learn everyday.

Actually, it is the smartest people that "learn everyday" ;-).

We have a poplulation of 1563 students.
Are program will only allow 1110 students. We have 3 tests we
work from. Test X has 146 students, test Y has 723 and test Z has 694.
Since we have only 1110 openings what I did was to divide the
1110/1563. This would give me a percentage of about 71.0172744721689%.
Then I took the figure and I divided each test group
146/71.0172744721689%, 723 /71.0172744721689% and the
694/71.0172744721689%. When rounded this gives me 104 children for test
X, 513 for test Y and 493 for text Z.

I think you multiplied, not divided, by 71%. For example, 146*71% ~=
104, but 146/71% ~= 206.

My question is, is there a way to
weight the different tests like test X =35%, test Y = 25% and test Z =
40% and have that come out to the 1110 students?

1110*35% (about 389) should come from test X. 1110*25% (about 278)
should come from test Y. 1110*40% (444) should come from test Z.

Note that that sums to 1111, not 1110. One way to ensure the proper
sum is to compute the last category not by 1110*40%, but effectively by
1110 - 389 - 278. It might also help to use so-called "banker's
rounding" (round xxx.50...0 to the nearest even number). But that does
not obviate the need to compute the last category in the aforementioned
manner in the general case -- albeit sufficient in this particular case
by coincidence.

Even Distribution of Students now with a Weighted Twist
 

David Biddulph wrote:
If you've got your number of students for the 3 tests in A1:A3, and your
weightings 35%,25%,40% in B1:B3, then you can produce a weighted number with
C1=A1*B1 and similarly down to C3. Your number of successful students from
test X could now become
=1110*C1/SUM(C$1:C$3) and you can copy that down to C2 & C3 (and deal with
rounding appropriately).

(I presume you mean that the last formula goes into D1 and is copied
down to D2 and D3.)

But doesn't D1 evaluate to 111 or 112 -- still more than the 104
students that took test X?

I think you can foresee the futility of any automatic redistribution if
you consider the possibility that fewer than a total of 1110 students
took tests X, Y and Z.

And even if at least a total 1110 students took tests X, Y and Z, who
is to say how we should redistribute the number of students accepted
from each test group if one or more groups are less than 35%, 25% or
40% of 1110 respectively?

I do not think the distribution is proportional to 35%, 25% and 40% of
each test group. For example, if exactly 1110 students took tests X, Y
and Z, we would accept 100% from each test group.

In PJ's example, we might accept all 104 students from test X, since
that is less than the 35% of 1110 (389) that we intended to accept from
test X. Note that 104 is 9.4% of 1110. Then we might redistribute the
remaining 90.6% in proportion to the original goal, namely: 34.8% and
55.8%, where 34.8% = 90.6%*25/(25+40).

But of course, that would fail if the number of students that took test
Y is less than 386 (34.8% of 1110). Moreover, the above implementation
is not sufficiently general to handle the case the test X group is
large enough, but not the test Y and/or test Z group.

The point is: when the data does not support the selection criteria, I
think someone needs to specify the (arbitrary) rules to handle the
situation. If this is a homework problem, study the problem
specifications or ask the instructor. When those rules are explained
here, perhaps we can then offer a paradigm, if not a solution.

In the meantime, perhaps the following would be sufficient, following
David's cell assignments:

D1: =if(A1<round(1110*B1,0), NA(), round(1110*B1,0))
D2: =if(A2<round(1110*B2,0), NA(), round(1110*B2,0))
D3: =if(A3<1110-D1-D2, NA(), 1110-D1-D2)

Even Distribution of Students now with a Weighted Twist
 

Errata....

I wrote:
David Biddulph wrote:
=1110*C1/SUM(C$1:C$3) and you can copy that down to C2 & C3 (and deal with
rounding appropriately).

(I presume you mean that the last formula goes into D1 and is copied
down to D2 and D3.)

But doesn't D1 evaluate to 111 or 112 -- still more than the 104
students that took test X?

My mistake: the number of test X students is 146 in PJ's posting, not
104. So David's formula does work with PJ's numbers (146, 723, 694).
But is that merely coincidence? For example, what if the test groups
were indeed 104, 513 and 493 (a total of 1110). Then as I said,
David's formula would return 111 or 112, which exceeds 104.

So I think my other comments are valid.

Even Distribution of Students now with a Weighted Twist
 

First of all I would like to thank you both, at least this gives me a
better perspective on how to approach this. Secondly, this is not a
homework assignment it is something we were asked to work on at my job.
And finally, there were no other directions or directives except that
they wanted the tests weighted to those weights. I thank you again for
all of the time that you both spent on this matter to help me!
wrote:
Errata....

I wrote:
David Biddulph wrote:
=1110*C1/SUM(C$1:C$3) and you can copy that down to C2 & C3 (and deal with
rounding appropriately).

(I presume you mean that the last formula goes into D1 and is copied
down to D2 and D3.)

But doesn't D1 evaluate to 111 or 112 -- still more than the 104
students that took test X?

My mistake: the number of test X students is 146 in PJ's posting, not
104. So David's formula does work with PJ's numbers (146, 723, 694).
But is that merely coincidence? For example, what if the test groups
were indeed 104, 513 and 493 (a total of 1110). Then as I said,
David's formula would return 111 or 112, which exceeds 104.

So I think my other comments are valid.

Even Distribution of Students now with a Weighted Twist
 

Even Distribution of Students now with a Weighted Twist
 

Am I correct that you (ideally) want 35%, 25% and
40% of 1110 to come from each test group respectively?

Yes, you are correct.


wrote:
wrote:
Secondly, this is not a
homework assignment it is something we were asked to work on at my job.

It would not have mattered (to me) if it were homework. The only point
I was making was: where you could go for further direction.

And finally, there were no other directions or directives except that
they wanted the tests weighted to those weights.

Then I believe they need to tell you -- unless you can make the
decision yourself -- what they want to do when their conditions form a
mathematical impossibility. Simply put: what if there are not 35% or
25% or 40% of 1110 in a test group?

By the way, one thing that was never clear to me about your
specification.... Am I correct that you (ideally) want 35%, 25% and
40% of 1110 to come from each test group respectively?

I cannot imagine any other interpretation that makes sense to me. But
if there is another sensible interpretation, that could make a lot of
difference in finding an adequate solution.