random number

[Hookster23]

Could someone help me with the following problem in excel:

I would like to be able to generate 20 random numbers whereby the
number generated is between say -15 and 40 where once all the numbers
are generated the average of all 20 numbers is 8 for example. I guess I
could generate 19 random numbers with the above parameters and the 20th
number would be a plug to achieve the correct average but was wondering
if there was another way.

Thank you for your help.


Answer can be either through using excel itself or through a macro code
for excel.

 

[Jim Cone]

Here is something you can play around with...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub FindNumbersThatAverage()
' Provides random numbers that average a predetermined amount.
' Jim Cone - San Francisco, USA - May 29, 2005
Dim lngN As Long
Dim lngLow As Long
Dim lngTemp As Long
Dim lngHigh As Long
Dim lngTarget As Long
Dim lngQuantity As Long
Dim lngArray() As Long

'Establish parameters...
lngLow = -15
lngHigh = 40
lngTarget = 8
lngQuantity = 20
'Sanity check
If lngLow > lngTarget Or lngHigh < lngTarget Then
Exit Sub
End If
'The number of numbers must be an even number <g>
If Not lngQuantity Mod 2 = 0 Then
lngQuantity = lngQuantity + 1
End If

ReDim lngArray(1 To lngQuantity)

For lngN = 1 To lngQuantity Step 2
'Get random values between the high and low parameters.
Randomize lngTemp
lngTemp = Int(Rnd * (lngHigh - lngLow + 1)) + lngLow

'Assign random values
lngArray(lngN) = lngTemp
lngArray(lngN + 1) = 2 * lngTarget - lngTemp

'If the high/low range is not centered on the target average
'then the random results may need adjusting.
If lngArray(lngN + 1) > lngHigh Then
lngArray(lngN) = 2 * lngTarget - lngHigh + lngN
lngArray(lngN + 1) = lngHigh - lngN
End If
If lngArray(lngN + 1) < lngLow Then
lngArray(lngN) = 2 * lngTarget - lngLow - lngN
lngArray(lngN + 1) = lngLow + lngN
End If
Next 'lngN

'Stick it on the worksheet.
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(1, lngQuantity).Value = lngArray()
End Sub
'-------------------


"Hookster23" <[email protected]>
wrote in message
Could someone help me with the following problem in excel:

I would like to be able to generate 20 random numbers whereby the
number generated is between say -15 and 40 where once all the numbers
are generated the average of all 20 numbers is 8 for example. I guess I
could generate 19 random numbers with the above parameters and the 20th
number would be a plug to achieve the correct average but was wondering
if there was another way.

Thank you for your help.
Answer can be either through using excel itself or through a macro code
for excel.

 

[Hookster23]

Thank you for your help