I would like to have Excel generate a range of random integers in cells A1 to
A40 and have the final average of that entire range = 4.2 (or any numeric
value I stipulate). I also want to place a constraint on each cell; that it
can only contain an integer and a minimum/maximum range of say, -6 to +14.
Is this possible? Thanks!

Steve C

does it have to be exactly 4.2 or do you want the numbers drawn from a
population that has an average of 4.2. What is the distribution you are
sampling from: Uniform, Normal, Triangular, Something else.

--
Regards,
Tom Ogilvy

"Steve C" wrote in message
...
I would like to have Excel generate a range of random integers in cells A1
to
A40 and have the final average of that entire range = 4.2 (or any numeric
value I stipulate). I also want to place a constraint on each cell; that
it
can only contain an integer and a minimum/maximum range of say, -6 to +14.
Is this possible? Thanks!

Steve C

Hi Steve,

A quick and naive approach:
Enter into cells A1:D4:
=INT(RAND()*(C2-C1+1))+C1 Min -6
=INT(RAND()*(C2-C1+1))+C1 Max 14
=INT(RAND()*(C2-C1+1))+C1 Average =AVERAGE(A1:A40) 4.2
=INT(RAND()*(C2-C1+1))+C1 Accuracy 0.05
Copy A4 down to A40.
Now you can push F9 until the average comes near to your desired result or
you insert a macro like:
Option Explicit

Public Sub try()
Dim i As Long

i = 1
With Sheets("Sheet1")
Do While i < 100
.Calculate
If Abs(.Cells(3, 3) - .Cells(3, 4)) <= .Cells(4, 3) Then
Exit Do
End If
i = i + 1
Loop
End With
End Sub

HTH,
Bernd

The best clarification I can think of would be that before cells A1 through
A40 are populated with random integers between -6 to +14, I would want to
enter a single value (i.e., 4.2, 3.8, 4.9, etc.) as the goal for that range
to average after it is populated. I don't know the answer to the sampling
distribution question you posed.

"Tom Ogilvy" wrote:

does it have to be exactly 4.2 or do you want the numbers drawn from a
population that has an average of 4.2. What is the distribution you are
sampling from: Uniform, Normal, Triangular, Something else.

--
Regards,
Tom Ogilvy

"Steve C" wrote in message
...
I would like to have Excel generate a range of random integers in cells A1
to
A40 and have the final average of that entire range = 4.2 (or any numeric
value I stipulate). I also want to place a constraint on each cell; that
it
can only contain an integer and a minimum/maximum range of say, -6 to +14.
Is this possible? Thanks!

Steve C

Thanks, Bernd, for your help. I did try your first approach, but found that
the average of A1:A40 fluctuated too much from achieving 4.2. I also ran
your code, but it didn't produce a result; I may not be understanding it the
way you intended it.

"Bernd Plumhoff" wrote:

Hi Steve,

A quick and naive approach:
Enter into cells A1:D4:
=INT(RAND()*(C2-C1+1))+C1 Min -6
=INT(RAND()*(C2-C1+1))+C1 Max 14
=INT(RAND()*(C2-C1+1))+C1 Average =AVERAGE(A1:A40) 4.2
=INT(RAND()*(C2-C1+1))+C1 Accuracy 0.05
Copy A4 down to A40.
Now you can push F9 until the average comes near to your desired result or
you insert a macro like:
Option Explicit

Public Sub try()
Dim i As Long

i = 1
With Sheets("Sheet1")
Do While i < 100
.Calculate
If Abs(.Cells(3, 3) - .Cells(3, 4)) <= .Cells(4, 3) Then
Exit Do
End If
i = i + 1
Loop
End With
End Sub

HTH,
Bernd

Hi Steve,

Hmm, my spreadsheet works fine:
One solution is
11 14 2 7 2 0 7 -4 0 10 13 -4 7 7 9 2 -4 3 0 10 -2 -3 7 9 -1 -5 7 0 1 4 7 3 7 -3 11 11 12 10 -5 6
Another
-2 -5 9 8 -1 0 6 12 9 -2 4 3 6 4 0 13 10 7 1 13 9 2 -4 -5 14 -1 9 -2 4 12 -1 -3 6 3 9 -2 4 6 -3 13
Did you place the =Average(A1:A40) exactly into cell C3 and the desired
value 4.2 into D3?
The macro is referring to these cells...
If you post your email address I can send you my file (make a verbal
description only to confuse spam harvester!).

HTH,
Bernd

The code is working now. I had to remove "Public" and then it did what I
needed. Thank you a ton!

"Bernd Plumhoff" wrote:

Hi Steve,

Hmm, my spreadsheet works fine:
One solution is
11 14 2 7 2 0 7 -4 0 10 13 -4 7 7 9 2 -4 3 0 10 -2 -3 7 9 -1 -5 7 0 1 4 7 3 7 -3 11 11 12 10 -5 6
Another
-2 -5 9 8 -1 0 6 12 9 -2 4 3 6 4 0 13 10 7 1 13 9 2 -4 -5 14 -1 9 -2 4 12 -1 -3 6 3 9 -2 4 6 -3 13
Did you place the =Average(A1:A40) exactly into cell C3 and the desired
value 4.2 into D3?
The macro is referring to these cells...
If you post your email address I can send you my file (make a verbal
description only to confuse spam harvester!).

HTH,
Bernd