Random range averaging
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
|