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-form...ml#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)),Val ues!$H$26:$H$32,Values!$J$26:$J$32)

=INDEX(J26:J36,MATCH(RANDBETWEEN(0,MAX(L26:L37)),L 26: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

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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)

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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