How do I find averages?

D

Daniel Lugones

I have a number, lets say 80. I want to find other values, lets say 10 number
values, between 50 and 100 such as that those numbers added have an average
of 80.

In other words:

a + b + c + d + e + f + g +... = 80
How can I find random values between 50 and 100 (a, b, c, d, etc) whose
average is 80.
Is it possible in Excel 2000?
thank you.
Daniel Lugones
 
G

Gary's Student

Put =RAND()*16 in cell A1 and =16-A1 in cell B1 .

Copy these cells down thru row 5. Now the sum of A1:B5, which is a set of
10 numbers, will always equal 80.

To get different numbers, just push F9.
 
J

Jim Cone

Daniel,

See if this comes close...
'---------------------------------
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 = 50
lngHigh = 100
lngTarget = 80
lngQuantity = 10

'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.
Range("A1", Cells(1, lngQuantity)).Value = lngArray()
End Sub
 
B

B. R.Ramachandran

Do you want the total or the average to be 80 (in the equation you have shown
the total as 80)?

By the very nature of this problem, only 9 of those 10 numbers can be truly
random (the 10th is constrained by the fact that the average of the 10
numbers has got to be 80).
Generate 9 random numbers between 50 and 100, using =(rand()+1)*50, say in
A1 thru A9. The 10th number (A10) is =800-sum(A1:A9). Due to the random
nature of the first 9 numbers, the 10th may not necessarily fall in the
required range, 50-100; hit F9 repeatedly till the 10th number also falls in
that range (this may need several attempts).

B.R.Ramachandran
 
Top