# Get 4 random numbers divided to 2 pairs with established max and min values to get given sum

#### Morty

##### New Member
Hello all,

I would like to politely ask you for help to solve my little problem. I have already worked with generating random numbers before and have tried to look up for solution, but this is probably more delicate problem than just generating random numbers.

Previously i used just this formula =RAND()*(C3-D3)+D3 to generate random numbers in given range (C3 MAX and D3 MIN).

But now its really not enough .

here is example:

Example.xlsx
ABCD
1Generate numbers
2Truck number 1MAXMIN
3AXIS 15 4055 0007 000
4AXIS 26 830
5AXIS 39 6907 00010 000
6AXIS 49 330
7
8sum weight31 255
9
11+
12truck weight14 691
List1
Cell Formulas
RangeFormula
B12B12=B8-B10

In every case for this particular truck, I will have same truck weight number and changing load weight number, which together give out sum weight number. And according to sum weight number I need it to split to 2 pairs of random generated numbers= AXIS 1,2 with given max and min values and AXIS 3,4 with also given range of max and min values. I think its solvedable, but still above my skill capabalities :D. I will use it for hundreds and hundreds cases .

Thank everyone in advance for any piece of help and wish you nice rest of the day.
Sincerely Morty

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
method 1 : randomselect for the 1st 3 values and the 4th is sum weight-sum(3 previous) and check if that number<10.000
method 2 : randomselect for all 4 values and divide the correction by 4 + add/substract it from the 4 digits
james.xlsb
ABCDE
1Generate numbers
2Truck number 1MAXMIN
3AXIS 1518050007000
4AXIS 25757
5AXIS 37411700010000
6AXIS 413216ONWAAR
7
8sum weight3156431564
9
11+
12truck weight15000
13
14
15
16
17
18
19
20Generate numbers
21Truck number 1MAXMIN
22AXIS 15832500070006910,5
23AXIS 255786656,5
24AXIS 385307000100009608,5
25AXIS 473108388,5
26correction
27sum weight27250315641078,531564
28
30+
31truck weight15000
32
Cell Formulas
RangeFormula
B3:B4,B22:B23B3=+\$C\$3+RANDBETWEEN(0,\$D\$3-\$C\$3)
B5,B24:B25B5=+\$C\$5+RANDBETWEEN(0,\$D\$5-\$C\$5)
B6B6=+C8-SUM(B3:B5)
E6E6=+(B6<D5)
B8,B27,E27B8=SUM(B3:B6)
C8,C27C8=+B10+B12
E22:E25E22=+B22+\$D\$27
D27D27=+(C27-B27)/4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B24:B25Expression=B24>\$D\$5textNO
B22:B23Expression=B22>\$D\$3textNO
B5:B6Expression=B5>\$D\$5textNO
B3:B4Expression=B3>\$D\$3textNO

Hi,

I Apologise for that i didn´t respond earlier, I have got too much work. Thank you very much BSALV, the second method is more than satisfing to solve my problem .

I wish you nice rest of the day and once again thank you .

With regards,
Morty

Replies
11
Views
1K
Replies
4
Views
396
Replies
6
Views
1K

1,196,115
Messages
6,013,558
Members
441,771
Latest member
clamnets

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back