Formula for numbers of distribution as per %...into specific roundup

[motilulla]

Using Excel 2010

Hello,

Formula In cell C10…=RAND() and copied to down C19

Formula In cell D10…=C10/SUM(C$10:C$19) and copied to down D19

Formula In cell E10 =$E$7*D10 and copied to down E19

Here below is a layout without formulas (because as F9 is pressed or any entry done value changes I have put formulas apart sorry for the trouble) in which column “E” cells E10:E19 this is a result of 20 numbers are in the cell E7, as per % of the column “D” cells D10:D19 and it sums total 20 shown in the E9.

What I need A formulas in the cells G, H, And I

1-G10:G19 so the distribution of the 20 numbers could be in (0.20, 0.40, 0.60, 0.80 and 1.0) as shown. Note: the total always must be 20 matches with E7 numbers

2-1-H10:H19 so the distribution of the 20 numbers could be in (0.25, 0.50, 0.75 and 1.0) as shown. Note: the total always must be 20 matches with E7 numbers

3-1-I10:I19 so the distribution of the 20 numbers could be in (0.50 and 1.0) as shown. Note: the total always must be 20 matches with E7 numbers

Is it possible?

Excel Question-New.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1							Distribution	Distribution	Distribution
2							Increase By	Increase By	Increase By
3							0,20	0,25	0,50
4							0,40	0,50	1,00
5							0,60	0,75
6					Distribution		0,80	1,00
7					20,00		1,00
8
9		Numbers	Rand %	100,00%	20,00		20,00	20,00	20,00
10		1	0,81708284	13,59%	2,72		2,60	2,50	2,50
11		2	0,06659038	1,11%	0,22		0,30	0,25	0,25
12		3	0,46226628	7,69%	1,54		1,40	1,50	1,50
13		4	0,88539852	14,72%	2,94		3,00	3,00	3,00
14		5	0,4678486	7,78%	1,56		1,60	1,50	1,50
15		6	0,99520141	16,55%	3,31		3,30	3,25	3,50
16		7	0,66660606	11,08%	2,22		2,20	2,25	2,00
17		8	0,95149739	15,82%	3,16		3,20	3,25	3,25
18		9	0,1519492	2,53%	0,51		0,60	0,75	0,50
19		10	0,54937649	9,14%	1,83		1,80	1,75	2,00
20
21
22
Sheet5

Please need help

Thank you all.

Regards,
Moti

 

[Sulprobil]

Hello Moti,

Instead of Rand() you could maybe use something like Max(0.1,Rand()). But keep in mind that you would change the shape of the random distribution.

Regards,
Bernd

 

[motilulla]

Hello Moti,

Instead of Rand() you could maybe use something like Max(0.1,Rand()). But keep in mind that you would change the shape of the random distribution.

Regards,
Bernd

Wow! Sulprobil, that is spot-on! Results are like a charm!

Note: changing the “Rand() to Max(0.1,Rand())” I noticed that if there are 10 numbers distribution to avoid the result “0.00” need to put distribution number in the cell E7 +1 more Instead 10…11 then all it works perfect

I appreciate your patience help and time you spend till solving end.

Good Luck

Here below are the best results of your solution.

MrExcel_Formula for numbers of distribution as per percentage into specific roundup.xlsm
	A	B	C	D	E	F	G	H	I
1							Distribution	Distribution	Distribution
2							5	4	2
3
4
5
6					Distribution
7					11,00
8
9		Numbers	Rand %	100,00%	11,00		11,00	11,00	11,00
10		1	0,37511777	7,36%	0,81		0,80	0,75	1,00
11		2	0,83039054	16,30%	1,79		1,80	1,75	1,50
12		3	0,44658422	8,76%	0,96		1,00	1,00	1,00
13		4	0,3420698	6,71%	0,74		0,80	0,75	0,50
14		5	0,50057195	9,82%	1,08		1,00	1,00	1,00
15		6	0,14159495	2,78%	0,31		0,20	0,25	0,50
16		7	0,64751546	12,71%	1,40		1,40	1,50	1,50
17		8	0,61422499	12,05%	1,33		1,40	1,25	1,50
18		9	0,42475248	8,34%	0,92		1,00	1,00	1,00
19		10	0,773101	15,17%	1,67		1,60	1,75	1,50
Sheet1

Cell Formulas
Range	Formula
E9,G9:I9	E9	=SUM(E10:E19)
G10:I19	G10	=RoundToSum($E$10:$E$19*G2,0)/G2
C10:C19	C10	=MAX(0.1,RAND())
D10:D19	D10	=C10/SUM(C$10:C$19)
E10:E19	E10	=$E$7*D10
Press CTRL+SHIFT+ENTER to enter array formulas.

Kind Regards,
Moti