randomization, averaging

shitalparikh

New Member
Joined
Feb 28, 2002
Messages
2
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
 
Upvote 0
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
 
Upvote 0
On 2002-03-02 20:43, ****alparikh 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
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top