Generate a set of random numbers with the specific AVERAGE

[BonScie]

Hi there, upon searching around the net I came across with Mr.Eric W.'s reply with one of the post here regarding generating random numbers with target overall total.

Link: generating a set of random numbers to total a set value

I have pretty much same problem but the target is AVERAGE of the generated random number instead of the overall total.

Your help will be much appreciated. Thank you in advance.

 

[BonScie]

I'm glad you're satisfied.

Hi again, Sir! With the VB code you made you put the specific needed values. I was thinking of a way on how can I refer it in a cell. Tried doing this but I guess it is not how it works ?. Kindly help me please... Thank you in advance!

vMin = 8.45 vMin = F2
vAverage = 11.55 vAverage = D2
vMax = 15.55 vMax = E2
vNumbers = 4 vNumbers = C2

Average_RNG.xlsm
	A	B	C	D	E	F	G
1	12.78		Base(n)	Average	Max	Min
2	9.19		4.00	11.55	15.55	8.45
3	13.02
4	11.21
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Jan_case1

Cell Formulas
Range	Formula
C2	C2	=COUNTIF(A1:A493,">0")

Named Ranges
Name	Refers To	Cells
Print_Area	=Jan_case1!$A$1:$G$153	C2
Print_Titles	=Jan_case1!$1:$7	C2

 

[BonScie]

No, you did nothing wrong, it's just a weakness of the algorithm I used. The way it works is it selects the first number in the accepted range. The next number it selects is more constrained since the total is fixed. I handle this by adjusting the acceptable upper and lower bounds for that number. After enough numbers, those acceptable bounds get closer and closer, until they converge on either the high or low bound. I don't know a good way to handle that with formulas only. I can think of a few ways using VBA, but it seems that EXCEL MAX has provided a workable macro already. Let me know if you want another option.

EXCEL MAX macro works great for me I just need something to make it easy to use. But thanks for the offer Sir. ?

 

[BonScie]

I'm glad you're satisfied.

Hi, Sir, I just want to share this. I made it work, I'm so happy ?
Thank you very much!

Average_RNG.xlsm
	A	B	C	D	E	F	G
1	5.72		Number	Average	Max	Min
2	5.93		4	6.25	13.25	5.00
3	6.54
4	6.81
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet1

 

[EXCEL MAX]

Just change this part in the code...
vMin = 8.45
vAverage = 11.55
vMax = 15.55
vNumbers = 298

with this...
vMin = Sheets("Jan_case1").[F2]
vAverage = Sheets("Jan_case1").[D2]
vMax = Sheets("Jan_case1").[E2]
vNumbers = Sheets("Jan_case1").[C2]

Congratulations....