Weighted randbetween text list

  • Thread starter Thread starter Xafen
  • Start date Start date
X

Xafen

A | B
1 Name | Weight
2 Bob | 1
3 Joe | 1
4 Sue | 5
5 Nick | 1
6 Ike | 1

I'm trying to setup a formula to return a random text string (values from column A) based on weighted values (column B). I'm trying to have a cell return Sue 5 times as often as the others.

I've been searching for a couple afternoons now to no avail. The closest I came was the following formula:
=INDEX(A:A,MATCH(RANDBETWEEN(0,MAX(C:C)),C:C,1))

from http://www.excelforum.com/excel-for...190-random-function-weighted.html#post1678471

But this would return reference errors because at some point, it would try to select value 8, but there were only 5 values.

I'm also tried modifying randbetween functions with counta and various other things.

=LOOKUP(RANDBETWEEN(1,SUM(Values!$I$26:$I$32)),Values!$H$26:$H$32,Values!$J$26:$J$32)

=INDEX(J26:J36,MATCH(RANDBETWEEN(0,MAX(L26:L37)),L26:L37,1))

Selecting a text value from a list at random is easy, this weighted part is giving me trouble.

Thanks for any guidance!
 
Hi,

Am Wed, 24 Dec 2014 10:32:49 -0800 (PST) schrieb Xafen:
A | B
1 Name | Weight
2 Bob | 1
3 Joe | 1
4 Sue | 5
5 Nick | 1
6 Ike | 1
Click to expand...

another suggestion:

=INDEX($A$2:$A$6,CHOOSE(TRUNC(RAND()*SUM(B:B)+1),1,2,3,3,3,3,3,4,5),0)


Regards
Claus B.
 
Hi,

Am Thu, 25 Dec 2014 19:55:29 +0100 schrieb Claus Busch:
=INDEX($A$2:$A$6,CHOOSE(TRUNC(RAND()*SUM(B:B)+1),1,2,3,3,3,3,3,4,5),0)
Click to expand...

or more regularly spread:
=INDEX($A$2:$A$6,CHOOSE(TRUNC(RAND()*SUM(B:B)+1),3,1,3,2,3,4,3,5,3),0)


Regards
Claus B.
 
Awesome! Thanks for the further pointers. After analyzing your formula it led me to the following:

=lookup(randbetween(1,sum(B2:B6)),C2:C6,A2:A6)

column C has a running sum of B, i.e. C2=sum(0,B2), C3=sum(C2,B3), etc.

In my formula column B is the weighted values, column A is the values, column C is the running total of B
 
Back
Top