randomization, averaging

[shitalparikh]

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?

[Autolycus]

On 2002-03-01 16:12, shitalparikh 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

[shitalparikh]

when i tried the first method, it shows #name? in all the 15 boxes.

when i tried the macro, it shows error run 1004. when i tried the debug- it shows arrow on the application part.

let me know what should i do next

thanks in advance

[Autolycus]

On 2002-03-02 20:43, shitalparikh wrote:
when i tried the first method, it shows #name? in all the 15 boxes.

when i tried the macro, it shows error run 1004. when i tried the debug- it shows arrow on the application part.

let me know what should i do next

thanks in advance

Go to Tools>Add-Ins and select AnalysisToolPak and AnalysisToolPak-VBA

that was great. thanks a lot