patelchandresh13

Estimable
Oct 15, 2015
8
1
4,510
Hie all,

Basically i am creating a excel sheet where i would be required to have random numbers in a particular range and in multiples for 5 and all the numbers generated should have the total which is entered. Let me elaborate

Input Number- 136400
Random Numbers Range- 12000 to 15000 (Range can be changed accordingly)
Random Numbers Generated
14250
13750
12900
12750
15000
14700
13930
14260
12860
12000

Random Number's total Should match the Input Number
Also the Random Number should not be repeated twice same in the same sequence.

I have Tried to have a look on various forums, but didn't got a proper match for what i was looking for.

Thanks,

 
Solution
Ok little bit weird but took a crack at, 3 inputs are going to be identified below (if it says formula the part in square brackets [] is a copy of the code I used, Asterisk * gives a brief description)

B1 - Input number *Using B Column so you can add labels if you want
B2 - Low Range
B3 - High Range

B5 to B13- Formula [=ROUND(RANDBETWEEN($B$2,$B$3),-1)] *Randbetween obviously for the values, round will make multiples of 10, best I could do, 9 random values between

B15 - Formula [B1-SUM(B5:B13)] *10th "Random" value which will give you your desired total
C15 - Formula [=IF(OR(B15<B2,B15>B3),"Failed","Success")] Checks if 10th value is within range, if Success then you have your 10 values, if Failed tell the sheet to calculate again or...

SU11YBEAR

Honorable
Jan 7, 2014
5
0
10,520
Ok little bit weird but took a crack at, 3 inputs are going to be identified below (if it says formula the part in square brackets [] is a copy of the code I used, Asterisk * gives a brief description)

B1 - Input number *Using B Column so you can add labels if you want
B2 - Low Range
B3 - High Range

B5 to B13- Formula [=ROUND(RANDBETWEEN($B$2,$B$3),-1)] *Randbetween obviously for the values, round will make multiples of 10, best I could do, 9 random values between

B15 - Formula [B1-SUM(B5:B13)] *10th "Random" value which will give you your desired total
C15 - Formula [=IF(OR(B15<B2,B15>B3),"Failed","Success")] Checks if 10th value is within range, if Success then you have your 10 values, if Failed tell the sheet to calculate again or make a change to have it auto calculate, when successful copy and paste values to get your ten numbers

Didn't do anything for the non-matching requirement, could use conditional formatting for a quick visual check but dont know an easy way to code this, hope this helps
 
Solution

patelchandresh13

Estimable
Oct 15, 2015
8
1
4,510


Thanks Very much for the formula's everything's working fine, i am just confused with the second formula which you have
[B1-SUM(B5:B13)] *10th "Random" value, which i could not understand as in what to do,

Rest all is well clear could you also please explain me.

Thanks again for the reply.
 

SU11YBEAR

Honorable
Jan 7, 2014
5
0
10,520
Happy to help, since you want them to total the input number the 10th value is just the input minus the other 9 that way the total of all 10 is always the input number
 

patelchandresh13

Estimable
Oct 15, 2015
8
1
4,510
Thanks Very much SU11YBEAR for the perfect Answer, as if for now what i have done is made a new button which would re-query the data. But it seems the code i which i have written is wrong and does not works as expected.