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)