CODEWITHSUNDEEP
excelvbaexcel-formula
arash
arash

Reputation: 29

Creating random numbers that their average be equal to M

I have N numbers in M columns that were created from 1 to 100 randomly. randbetween (1;100). I got their mean. Now having the mean of N numbers I want create from 1 to 100 randomly that their averags be equal to Mean (inversely) [Attached file]

Upvotes: 0

Views: 199

Answers (2)

arash
arash

Reputation: 29

I have found the code that creates random numbers by target sum.

 Function RandTot(iTot As Long, iLo As Long, iHi As Long, _
                 Optional bVol As Boolean = False) As Variant
    Dim nNum        As Long
    Dim i           As Long
    Dim ad()        As Double
    Dim iTry        As Long
    If bVol Then Application.Volatile
    With Application.Caller
        If .Rows.Count > 1 And .Columns.Count > 1 Then
            RandTot = "Enter as row or column vector only!"
            Exit Function
        End If
        nNum = .Count
    End With
    If iHi < iLo Or _
       iTot < nNum * iLo Or _
       iHi > iTot Then
        RandTot = CVErr(xlErrValue)
        Exit Function
    End If
    ReDim ad(1 To nNum)
    Randomize
    With WorksheetFunction
        Do
            iTry = iTry + 1
            If iTry > 200 Then
                RandTot = "Time-out"
                Exit Function
            End If
            For i = 1 To nNum - 1
                ad(i) = RandBetw(iLo, iHi)
            Next i
            ad(i) = iTot - .Sum(ad) + ad(i)
        Loop Until .Min(ad) >= iLo And .Max(ad) <= iHi
    End With
    RandTot = ad
End Function
Function RandBetw(iLo, iHi) As Long
    RandBetw = (Rnd * (iHi - iLo) + Rnd * (iHi - iLo) + Rnd * (iHi - iLo)) / 4 + iLo
End Function

First I multiply target average in N then I use this code, but sometimes giving me Time-Out.

Upvotes: 1

Dominique
Dominique

Reputation: 17493

  1. Create N random numbers.
  2. Calculate the average => A.
  3. Add M-A to the random numbers you've generated.

Upvotes: 4

Related Questions