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

[Morty]

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
	A	B	C	D
1	Generate numbers
2	Truck number 1		MAX	MIN
3	AXIS 1	5 405	5 000	7 000
4	AXIS 2	6 830
5	AXIS 3	9 690	7 000	10 000
6	AXIS 4	9 330
7		↑
8	sum weight	31 255
9		↑
10	load weight	16 564
11		+
12	truck weight	14 691
List1

Cell Formulas
Range	Formula
B12	B12	=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

 

[BSALV]

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
	A	B	C	D	E
1	Generate numbers
2	Truck number 1		MAX	MIN
3	AXIS 1	5180	5000	7000
4	AXIS 2	5757
5	AXIS 3	7411	7000	10000
6	AXIS 4	13216			ONWAAR
7		↑
8	sum weight	31564	31564
9		↑
10	load weight	16564
11		+
12	truck weight	15000
13
14
15
16
17
18
19
20	Generate numbers
21	Truck number 1		MAX	MIN
22	AXIS 1	5832	5000	7000	6910,5
23	AXIS 2	5578			6656,5
24	AXIS 3	8530	7000	10000	9608,5
25	AXIS 4	7310			8388,5
26		↑		correction
27	sum weight	27250	31564	1078,5	31564
28		↑
29	load weight	16564
30		+
31	truck weight	15000
32
Blad2

Cell Formulas
Range	Formula
B3:B4,B22:B23	B3	=+$C$3+RANDBETWEEN(0,$D$3-$C$3)
B5,B24:B25	B5	=+$C$5+RANDBETWEEN(0,$D$5-$C$5)
B6	B6	=+C8-SUM(B3:B5)
E6	E6	=+(B6<D5)
B8,B27,E27	B8	=SUM(B3:B6)
C8,C27	C8	=+B10+B12
E22:E25	E22	=+B22+$D$27
D27	D27	=+(C27-B27)/4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B24:B25	Expression	=B24>$D$5	text	NO
B22:B23	Expression	=B22>$D$3	text	NO
B5:B6	Expression	=B5>$D$5	text	NO
B3:B4	Expression	=B3>$D$3	text	NO

 

[Morty]

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