Generate a random number based on three columns of various probabilities

javajoe · Nov 14, 2017

I want to generate a random number between 1 and X (where X is a dynamic number of varying length/range) in one of three columns (obviously using RANDBETWEEN) but then controlled by a manually-driven weighting factor assigned to each column I can change at any time.

So far example, if A1 is 70%, B1 is 20%, and C1 is 10%, then generally speaking 70% of the time I want it to give me a random number from 1-5 from column A, 20% of the time I want to return a number between 1-9, and roughly 10% of the time I want it to randomly return a number between 1-3.

70%	20%	10%
1	1	1
2	2	2
3	3	3
4	4
5	5
	6
	7
	8
	9

Thanks in advance!

JoeMo · Nov 14, 2017

Re: Need to generate a random number based on three columns of various probabilities

This code will place the headers: 70%, 20%,10% in A1:C1 then generate random numbers from 1 to 5 in column A, from 1 to 9 in col B and from 1 to 3 in col C. such that if you run 1 cycle (NumCycles = 1) col A will have 70 random numbers from 1 - 5, col B 20 random numbers from 1 to 9, and col C 10 random numbers from 1 - 3.

Code:

Sub WeightedRandNum()
Const NumCycles As Long = 1 'Set number of weighted random number generation cycles to run
Const weight1 As Double = 0.7  'set the weights
Const weight2 As Double = 0.2
Const weight3 As Double = 0.1
Dim Ct1 As Long, Ct2 As Long, Ct3 As Long, V1(), V2(), V3(), nRA As Long, nRB As Long, nRC As Long, Cycles As Long
Ct1 = weight1 * 100: Ct2 = weight2 * 100: Ct3 = weight3 * 100
ReDim V1(1 To Ct1): ReDim V2(1 To Ct2): ReDim V3(1 To Ct3)
Application.ScreenUpdating = False
Range("A:C").ClearContents
Range("A1:C1").Value = Array(Ct1 & "%", Ct2 & "%", Ct3 & "%")
Do
    Cycles = Cycles + 1
    For i = 1 To Ct1
        V1(i) = WorksheetFunction.RandBetween(1, 5)
    Next i
    For i = 1 To Ct2
        V2(i) = WorksheetFunction.RandBetween(1, 9)
    Next i
    For i = 1 To Ct3
        V3(i) = WorksheetFunction.RandBetween(1, 3)
    Next i
    nRA = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & nRA, "A" & nRA + Ct1 - 1).Value = Application.Transpose(V1)
    nRB = Range("B" & Rows.Count).End(xlUp).Row + 1
    Range("B" & nRB, "B" & nRB + Ct2 - 1).Value = Application.Transpose(V2)
    nRC = Range("C" & Rows.Count).End(xlUp).Row + 1
    Range("C" & nRC, "C" & nRC + Ct3 - 1).Value = Application.Transpose(V3)
Loop While Cycles < NumCycles
Application.ScreenUpdating = True
End Sub

Rick Rothstein · Nov 15, 2017

Re: Need to generate a random number based on three columns of various probabilities

If I understand the request correctly, there are percentages already in cells A1:C1 and values in the cells under them and you want to randomly select one of those columns but that selection should be weighted by the percent shown in Row 1. So, for the example given, and for a significantly large number of tries, Column A would be selected 70% of the time, Column B would be selected 20% of the time and Column C would be selected 10% of the time... and, once a column is selected, a random filled cell in that column would be selected as the final result. The following UDF (user defined function) will do that. The UDF contains an optional argument that lets you control whether the function will be Volatile or not within the formula that contains it. By default, the value returned by the UDF will be fixed and non-changing until the formula containing the UDF is manually executed again (that is, the UDF will act as if it were not Volatile). You can also pass FALSE as the UDF's argument to force this behavior, but if you omit the argument, the UDF will assume the argument to be FALSE by default. This is unlike how Excel's RND function works which changes every time the worksheet is recalculated. If you want RND's ever changing functionality (known as being Volatile) to be in effect for the formula containing the UDF, then pass TRUE as its argument. You can call the UDF differently in different formulas making some of the values the formulas containing the UDF return as being fixed and unchanging while other formulas containing the UDF are Volatile and change each time the sheet is recalculated. I did not know which functionality you wanted, so I gave you the ability to have either or both as your needs dictated. Here is the code for the UDF...

Code:

[table="width: 500"]
[tr]
	[td]Function WeightedRnd(Optional IsVolatile As Boolean) As Variant
  Dim R As Long, C As Long, Selector As String
  Application.Volatile IsVolatile
  Randomize
  Selector = String(100 * [a1], "1") & String(100 * [b1], "2") & String(100 * [c1], "3")
  C = Mid(Selector, Int(100 * Rnd + 1), 1)
  R = Int((Cells(Rows.Count, C).End(xlUp).Row - 1) * Rnd + 2)
  WeightedRnd = Cells(R, C).Value
End Function[/td]
[/tr]
[/table]

NOTE: This UDF requires the value in cells A1, B1 and C1 to be real percentages.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example,

Not Volatile: =WeightedRnd()

Not Volatile: =WeightedRnd(FALSE)

Volatile: =WeightedRnd(TRUE)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Marcelo Branco · Nov 15, 2017

Re: Need to generate a random number based on three columns of various probabilities

Maybe something like this

	A	B	C	D	E	F
1	Group	1	2	3
2	%	70%	20%	10%
3	Numbers	5	9	3		Random Num
4	1	x	x	x		3
5	2	x	x	x
6	3	x	x	x
7	4	x	x
8	5	x	x
9	6		x
10	7		x
11	8		x
12	9		x

Formula in B3 copied across to D3
=COUNTA(B4:B12)

Formula in F4
=RANDBETWEEN(1,LOOKUP(RANDBETWEEN(1,100),CHOOSE({1,2,3},1,B2*100+1,(B2+C2)*100+1),B3:D3))

M.

javajoe · Nov 16, 2017

Re: Need to generate a random number based on three columns of various probabilities

Ok thanks everyone. We're getting there but not quite right yet. Sorry for any confusion. Here is the actual thing I'm trying to accomplish:

User should be able to manually enter a "difficulty rating" in cells A1:C1, and together they will add up to 100%. This controls the overall difficulty or probability of a word being selected from each of the three colored list of spelling words. So in the example below, we generally want a RED spelling word to be returned around 70% of the time, an orange word selected generally 20% of the time, and one of the yellow words only selected around 10% of the time. Or it could be 100%, 0%, 0% in which case a word is randomly selected but only from the RED list 100% of the time. Note also that each list of spelling test words could be in a range of varying length.

The random word selected should be returned in cell C26.

	A	B	C
1	70%	20%	10%
2	RED	ORANGE	YELLOW
3	a	and	Cherry
4	and	big	for
5	be	but	yes
6	blue	by	jump
7	cat	has	get
8	do	he	down
9	dog	her	did
10	go	him	are
11	is	his	have
12	like	in	no
13	she	little	one
14	will	on
15	too	play
16	at	up
17	can	with
18	see	you
19	the
20	was
21
22
23
24
25
26		Random Word Selected:	[returned here]

javajoe · Nov 16, 2017

Re: Need to generate a random number based on three columns of various probabilities

Formula in B3 copied across to D3
=COUNTA(B4:B12)

Formula in F4
=RANDBETWEEN(1,LOOKUP(RANDBETWEEN(1,100),CHOOSE({1,2,3},1,B2*100+1,(B2+C2)*100+1),B3:D3))

This formula is very close but I need it to return the actual word selected and it appears to be returning the row in the dynamic range. We're getting close though!

Rick Rothstein · Nov 16, 2017

Re: Need to generate a random number based on three columns of various probabilities

Here is the code I posted earlier (with a change to correct a minor calculation error) adjusted to your above posted layout. I see you have a duplicated word so, on the off chance there could be others, I chose to have the function output the color from Row 2 along with the selected word so that you could see which column it came from. If you don't want that "feature", simply deleted the part of the code I highlighted in red below.

Code:

[table="width: 500"]
[tr]
	[td]Function WeightedRnd(Optional IsVolatile As Boolean) As Variant
  Dim R As Long, C As Long, Selector As String
  Application.Volatile IsVolatile
  Randomize
  Selector = String(100 * [a1], "1") & String(100 * [b1], "2") & String(100 * [c1], "3")
  C = Mid(Selector, Application.RandBetween(1, 100), 1)
  R = Application.RandBetween(3, Cells(1, C).End(xlDown).Row)
  WeightedRnd = Cells(R, C).Value[B][COLOR="#FF0000"] & " (" & Cells(2, C).Value & ")"[/COLOR][/B]
End Function[/td]
[/tr]
[/table]

This function is used in the same way I described in Message #4 above. Namely, put this in cell C26...

=WeightedRnd()

if you don't want the value to change once displayed; or put this in cell C26 if you do want it to change when the sheet is recalculated (such as when you press the F9 key)...

=WeightedRnd(TRUE)

Marcelo Branco · Nov 16, 2017

Re: Need to generate a random number based on three columns of various probabilities

javajoe said:
This formula is very close but I need it to return the actual word selected and it appears to be returning the row in the dynamic range. We're getting close though!

Maybe this...

	A	B	C	D	E	F	G
1	Group#	1	2	3		Random Group#	Random Word
2	%	70%	20%	10%		1	dog
3	Group Name	RED	ORANGE	YELLOW
4	Words Count	18	16	11
5		a	and	Cherry
6		and	big	for
7		be	but	yes
8		blue	by	jump
9		cat	has	get
10		do	he	down
11		dog	her	did
12		go	him	are
13		is	his	have
14		like	in	no
15		she	little	one
16		will	on
17		too	play
18		at	up
19		can	with
20		see	you
21		the
22		was
23

Formula in B4 copied across
=COUNTA(B5:B22)

Formula in F2 - generates random Group# according to % in B2:D2
=LOOKUP(RANDBETWEEN(1,100),CHOOSE({1,2,3},1,B2*100+1,(B2+C2)*100+1),B1:D1)

Formula in G2 - generates random word in the group drawn (F2)
=INDEX(B5:D22,RANDBETWEEN(1,INDEX(B4:D4,MATCH(F2,B1:D1))),MATCH(F2,B1:D1))

M.

Marcelo Branco · Nov 16, 2017

Re: Need to generate a random number based on three columns of various probabilities

Or

	A	B	C	D	E	F	G
1	%	70%	20%	10%		Random Group Name	Random Word
2	Group Name	RED	ORANGE	YELLOW		RED	will
3	Words Count	18	16	11
4		a	and	Cherry
5		and	big	for
6		be	but	yes
7		blue	by	jump
8		cat	has	get
9		do	he	down
10		dog	her	did
11		go	him	are
12		is	his	have
13		like	in	no
14		she	little	one
15		will	on
16		too	play
17		at	up
18		can	with
19		see	you
20		the
21		was
22

Formula in B3 copied across
=COUNTA(B4:B21)

Formula in F2
=LOOKUP(RANDBETWEEN(1,100),CHOOSE({1,2,3},1,B1*100+1,(B1+C1)*100+1),B2:D2)

Formula in G2
=INDEX(B4:D22,RANDBETWEEN(1,INDEX(B3:D3,MATCH(F2,B2:D2,0))),MATCH(F2,B2:D2,0))

M.

javajoe · Nov 17, 2017

Re: Need to generate a random number based on three columns of various probabilities

Excellent thank you Marcelo Branco! Close enough to get me rolling thank you everyone!

Generate a random number based on three columns of various probabilities

javajoe

Board Regular

Excel Facts

JoeMo

MrExcel MVP

Rick Rothstein

MrExcel MVP

Marcelo Branco

MrExcel MVP

javajoe

Board Regular

javajoe

Board Regular

Rick Rothstein

MrExcel MVP

Marcelo Branco

MrExcel MVP

Marcelo Branco

MrExcel MVP

javajoe

Board Regular

Similar threads

Forum statistics

Share this page

Generate a random number based on three columns of various probabilities

Board Regular

Excel Facts

MrExcel MVP

MrExcel MVP

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

MrExcel MVP

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock