Goal seek to get the same value in certain cell

[KingTamo]

Hi excel experts
in cell a1 there is a value 34156 and I put some formulas to distribute the value to certain values 200,100,50,20,10,5,1
How to generate random numbers in column d but with a criteria to get the same value 34156 in last cell in column e in cell E11?

[Hydraulics]

Here is a possible solution. The last number cannot be random and must be calculated, all the others are function of the sum still available.

In D5

=RANDBETWEEN(1,INT($A$1/C5))

In D6:D10

=RANDBETWEEN(1,INT(($A$1-E5)/C6))

HTH,

Francesco

[KingTamo]

Great. Thanks a lot
Is it probable to make cell d11 less than certain number (e.g cell D11 should be less than or equal 50)

[Hydraulics]

File updated and slightly changed, so that we are sure to always have a least a 1 in column D for each value.

HTH,

Francesco

[KingTamo]

Thanks a lot
In cell D10, there is 56 and 45 in the formula, what does this refer to exactly?

[Hydraulics]

The first is your min value (50) plus the min sum of all remaining elements (5*1+1*1); the second is 50 decreased by the current value (5). They are needed to have in E11 a number that is always between 1 and 50 (actually, 46).

=RANDBETWEEN(IF($A$1-E9>50+(C10+C11),INT(($A$1-SUM(C11:$C$11)-E9-(50-C10))/C10),1),INT(($A$1-SUM(C11:$C$11)-E9)/C10))

You can change the 50 with something else, and that will be the max value for E11.

HTH,

Francesco

[KingTamo]

Thanks a lot for your help