How can I return a list of "ASELECT" values within a range I specify and an average I specify ?

No, sorry Pasqual I personally have no idea what you mean.

Perhaps an example of the output you are expecting may prove useful?

Thanks.

BFN,

fp.

I made an example here: www.multifire.nl/example.xls

Hi Pasqual,

Thanks for your time producing the example.

Hmmm... a very 'interesting' challenge :)

How do you wish to specify the number of rows to fill (e.g. 27 in the case of your example.xls)?

Also, will the output always be from row 9 downwards in column C?

Finally, do you wish the random numbers to be sorted into ascending or descending order in column C, or should they just be shown as they are generated (i.e. in an unsorted order).

Thanks again for your confirmation.

BFN,

fp.

I think the only way is to generate the numbers between the ranges in VBA, and loop it so it does it again and again until the average is the one requested.

Pasqual,

Here is a routine that returns random numbers evenly distributed between limits with a specified average.

I had to choose between returning numbers that have the specified average (using MIN function) and those evenly distributed between upper and lower limits (using MAX function). When the average is different from (upper limit + lower limit)/2    then the first method will fails to return numbers at one extreme or the other of the range; the second method fails to return numbers with the right average.

Function myDistribution(Avg, UpperLimit, LowerLimit) As Double
Dim span As Double, i As Long
span = Application.Min(UpperLimit - Avg, Avg - LowerLimit)
For i = 1 To 10
myDistribution = Rnd() * 2 * span + (Avg - span)
If myDistribution >= LowerLimit And myDistribution <= UpperLimit Then Exit Function
Next i
End Function

Hi,

Whilst I puzzle over a more robust VBA solution, insert the following code into a global code module, e.g. "Module1":

Option Explicit
Public Function Calc_Average(ByVal dblValue As Double, objRange As Range) As Double

  Dim dblAverage                                        As Double
 
  On Error Resume Next
 
  dblAverage = CDbl(Application.WorksheetFunction.Average(objRange))
 
  While (dblAverage <> dblValue)
 
      DoEvents
     
      Randomize
     
      Application.CalculateFull
     
      dblAverage = CDbl(Application.WorksheetFunction.Average(objRange))
       
  Wend
 
  Application.StatusBar = ""
 
  Calc_Average = dblValue
 
End Function



Then in your worksheet enter the following formula into the indicated cells:

C2 =Calc_Average(203, C9:C35) [e.g. 203 is the average required]
C3 =100 [as above]
C4 =300 [as above]

C9:C35 (i.e. all cells in this range) =INT(RAND() * (C$4-C$3) + C$3)

And then watch the pretty display of changing numbers until the result is reached :)

Note: This may take some time.

Should you wish to stop the number generation mid-flow just type the value of C3 (i.e. 100) into cell C4 so that both cells read 100.

BFN,

fp.

Wow, what a bunch of enthousiastic people here! :)

I tried implementing fanpages' solution, but somehow it didn't work....

I created the module and saved it within the example file.
Filled in the Cells:

C2 >> "Calc_Average(203, C9:C35)"
C3 >> "100"
C3 >> "300"

"And then watch the pretty display of changing numbers until the result is reached"......

Well.... I think I watched long enough, but nothing happened :)

It must have something to do with the value in cell C2. At which moment should the number generation start ?

Pasqual

Hi,

Cell C2 should read
=Calc_Average(203, C9:C35)

Note that the prefix equality sign ("=") needs to be there.

Sorry if that wasn't clear from my last posting.

As soon as you press [Enter] the numbers should start to churn...

Alternatively, enter 203 into cell C2
and in cell D2 enter
=Calc_Average(C2, C9:C35)

Either method should work.

Please try again & let me know how you get on.

If you are still having trouble I could make my working ".xls" file available on the web for you to download.

Thanks.

BFN,

fp.

Pascual,

Both fanpages' code and mine use a uniform distribution, and so will suffer the problem of having either the range or average different from your request. If this is not acceptable, then you will need another type of distribution (normal, Weibull, binomial, etc.) which is concentrated near the mean and tapers off to the extremes.

Brad

Fanpages,

I installed the module, restarted excel...everything ok, but
as soon I I  enter '=Calc_Average(C2, C9:C35)' into cell C2 and press ENTER, I recieve an error message saying "the formula you entered contains a mistake... bla bla"

BTW, I'm using a dutch version of excel, but that shouldn't matter I guess. (I did translate the the functions in cell C9:C35)

It would help me a lot if you could make your version available for download :)

Thanks,

Pasqual

Hi again,

Will have something available for you to download later this afternoon.

Sorry for the delay... "proper" work called :)

BFN,

fp.

Sure, take it easy...

My working time is almost over (here in Holland), so I'll see it tomorrow ! ;)

Thanks,

Pasqual

Hmm, here at home it doesn't work.... but my excel 2003 is a having some troubles lately.
I'll try it tomorrow @ at work on excel 2002 :-)

Regards,

Pasqual

(00:15 UK time)

Hi Pasqual,

Thanks for the grading/points.

I have tried the above ".xls" file in Excel 2000, and Excel 2002, without any problems, so hopefully you should be OK tomorrow at work.

If not, please come back to me.

BFN,

fp.

Works great ! Thanks a lot :)

Pasqual

You're very welcome :)

PS. The hyperlinks to the MS-Excel workbooks above are now invalid.

The same files are attached to this comment.

BFN,

fp.
Q-20769947.xls
example.xls