# How can I sum up “n” randomly selected cells in Excel?

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.

• 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

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)

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