1

Using Excel 2010, I have the following problem:

I have one column with integers (from a binomial distribution) to which I will refer as "n". In a second sheet, I have another column of values (from a cost analysis). Now I want Excel to randomly pick n-cells (n = a given integer) from sheet 2 and sum-them up.

E.g. Sheet 1, Column A
4
6
7
8
3
4
10
etc.

Sheet 2; Column A
345
221
1011
223
455
12
...

I would like to create a column now where excel took e.g. 4 (for the first line) random, different values of sheet two (e.g. 221, 455, 12 and 223) and sums them up automatically, so the result in the respective should be 911. For the next cell, Excel should sum up 6 random values, etc.

Is this possible?

Using the INDEX-function, I have managed to tell Excel to pick one random cell from sheet 2, or 5 times the same, but not n cells and to sum them up.

  • 2
    Welcome to the site. Please edit your question to include an example of the formula you are working with and what version of Excel. – CharlieRB Oct 20 '17 at 12:01
  • Can you share your existing formula with us? – CharlieRB Oct 20 '17 at 12:47
0

Consider the following User Defined Function:

Public Function ransum(HowMany As Long, Population As Range) As Long
    Dim i As Long, N As Long, aray(), r As Range
    N = Population.Count
    ReDim aray(1 To N)

    i = 1
    For Each r In Population
        aray(i) = r.Value
        i = i + 1
    Next r

    Call Shuffle(aray)

    ransum = 0
    For i = 1 To HowMany
        ransum = ransum + aray(i)
    Next i
End Function

Public Sub Shuffle(InOut() As Variant)
    Dim i As Long, J As Long
    Dim tempF As Double, Temp As Variant

    Hi = UBound(InOut)
    Low = LBound(InOut)
    ReDim Helper(Low To Hi) As Double
    Randomize

    For i = Low To Hi
        Helper(i) = Rnd
    Next i


    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For i = Low To Hi - J
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        For i = Hi - J To Low Step -1
          If Helper(i) > Helper(i + J) Then
            tempF = Helper(i)
            Helper(i) = Helper(i + J)
            Helper(i + J) = tempF
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        J = J \ 2
    Loop
End Sub

So in Sheet1 cell B1 we enter:

=ransum(A1,Sheet2!A$1:A$100)

and copy down:

enter image description here

This assumes that the data to be sampled is in cells A1 through A100 of the second sheet.

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

  • That seems to have solved my problem! Thank you VERY MUCH!!! – Felix Oct 20 '17 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.