LinkBack URL
About LinkBacks
I need a function (or VBA solution) to calculate the sum of a defined number of random selections from a list.
So essentially function(2,A1:A5) would return the sum of 2 random numbers from A1:A5
Please help
Sum of random selection from a list (no helper column)
I need a function (or VBA solution) to calculate the sum of a defined number of random selections from a list.
So essentially function(2,A1:A5) would return the sum of 2 random numbers from A1:A5
Please help
Last edited by balper; 12-21-2010 at 07:33 PM.
Hi,
How about
=SUM(INDEX(A1:A5,RANDBETWEEN(1,5)),INDEX(A1:A5,RANDBETWEEN(1,5)))
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
I need the number of selections to be an input, because I am referencing a different cell for it.
Hi,
You might have some luck with this UDF
Usage = RandomSum(Number of cells to randomly sum,Range)
Please Login or Register to view this content.
eg, for your initial example
= RandomSum(2,A1:A5)
and your next requirement? can it be the same number twice?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Works! Thanks so much! [Scratch that, doesn't work]
Last edited by balper; 12-21-2010 at 01:53 PM.
Hi actually this function is not working, I tried some simple tests and it is definitly not returning random selections.
The function returns selections that are as random as the rnd function allows (computers can't generate a truly random number). There is much documentation on the web that refers to the methodology that excel uses to generate a random number, and a search of this forum may yield a way to generate a "more random" random number.
I actually think there may be an error in the code.
When I run the function: RANDOMSUM(2,$A$1:$A$10) ten different times for:
A1=1, A2=2, A3=3... A10=10.
It returns 3,4,2,4,3,3,3,4,3,3
Apologies, you're absolutely correct. Please find amended code below.
Is it christmas yet?Please Login or Register to view this content.
Bingo.
Hohoho
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks