randomization, averaging
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: randomization, averaging

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?

  2. #2

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Guest

    Default

      
    that was great. thanks a lot

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com