Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Even Distribution of Students now with a Weighted Twist
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Even Distribution of Students now with a Weighted Twist
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Weighted Average Using Row Number | Excel Worksheet Functions | |||
Weighted Avg | Excel Discussion (Misc queries) | |||
Normal Distribution line | Excel Discussion (Misc queries) | |||
Normal Distribution line | Excel Discussion (Misc queries) | |||
How can I use Excel to schedule students for classes? | Excel Discussion (Misc queries) |