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

1 Answer

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:

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:

and

(v=office.14).aspx

and for specifics on UDFs, see:

Macros must be enabled for this to work!

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

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