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 = 70
lngHigh = 100
lngTarget = 83
lngQuantity = 24
'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
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
"emvpix"
<
[email protected]>
wrote in message
I need to generate a list of numbers that fall within a certain range based
on a "base" percentage. I am generating a sample gradebook. Student "A" has a
semester grade of 83%. I need to generate a list of 24 "assignments" that
would average out to that 83% semester grade -- the student would get a few
95%; a couple of 70%; but the average would be 83 (or some given number) I am
certain there is an easy way to do this in excel . . .
