On 2002-03-01 16:12, ****alparikh wrote:
the highest number in my data is 6.6
the lowest number in my data is 2.3
the average of 15 such numbers is 4.6
how can i derive these 15 random numbers?
Assuming that the 15 numbers do not have to be unique and that they should be to 1 decimal place, go to Tools>Options>Calculation and select Iteration (max iterations of 100 should be enough).
Select cells A1:A15, type the following and press Ctrl+Enter :-
=IF(AVERAGE($A$1:$A$15)<>4.6,RANDBETWEEN(23,66)/10,A1)
If the average of A1:A15 does not equal 4.6, run the process again by selecting any cell in A1:A15, pressing F2, pressing Enter.
To fix the random numbers, select A1:A15, copy, pastespecial/values.
Alternatively, you could use a macro which would avoid having to select Iteration and then de-selecting it, and should also always produce numbers averaging 4.6 :-
Dim rng As Range
Set rng = [A1:A15]
Application.Iteration = True
With rng
Do
.FormulaR1C1 = "=IF(AVERAGE(R1C1:R15C1)<>4.6,RANDBETWEEN(23,66)/10,RC)"
If Application.WorksheetFunction.Average([rng]) = 4.6 Then Exit Do
Loop
.Value = .Value
End With
Application.Iteration = False
