Generate a random number based on three columns of various probabilities

javajoe

javajoe

Board Regular
Joined
Nov 7, 2005
Messages
78
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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]70%
[/TD]
[TD]20%
[/TD]
[TD]10%
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]8
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
Re: Need to generate a random number based on three columns of various probabilities

Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Group​
[/TD]
[TD]
1​
[/TD]
[TD]
2​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
%​
[/TD]
[TD]
70%​
[/TD]
[TD]
20%​
[/TD]
[TD]
10%​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Numbers​
[/TD]
[TD]
5​
[/TD]
[TD]
9​
[/TD]
[TD]
3​
[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]
Random Num​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
1​
[/TD]
[TD]
x​
[/TD]
[TD]
x​
[/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD="bgcolor: #D9D9D9"]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
2​
[/TD]
[TD]
x​
[/TD]
[TD]
x​
[/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
3​
[/TD]
[TD]
x​
[/TD]
[TD]
x​
[/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
4​
[/TD]
[TD]
x​
[/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
5​
[/TD]
[TD]
x​
[/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
9​
[/TD]
[TD][/TD]
[TD]
x​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
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.

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64, align: center"][/TD]
[TD="width: 64, align: center"]A
[/TD]
[TD="width: 64, align: center"]B[/TD]
[TD="width: 64, align: center"]C
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="class: xl65, align: right"]70%
[/TD]
[TD="class: xl65, align: right"]20%[/TD]
[TD="class: xl65, align: right"]10%
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="class: xl63, width: 64"]RED[/TD]
[TD="class: xl63, width: 64"]ORANGE
[/TD]
[TD="class: xl63, width: 64"]YELLOW[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="class: xl64"]a[/TD]
[TD="class: xl64"]and[/TD]
[TD="class: xl64"]Cherry[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="class: xl64"]and[/TD]
[TD="class: xl64"]big[/TD]
[TD="class: xl64"]for[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="class: xl64"]be[/TD]
[TD="class: xl64"]but[/TD]
[TD="class: xl64"]yes[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="class: xl64"]blue[/TD]
[TD="class: xl64"]by[/TD]
[TD="class: xl64"]jump[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="class: xl64"]cat[/TD]
[TD="class: xl64"]has[/TD]
[TD="class: xl64"]get[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="class: xl64"]do[/TD]
[TD="class: xl64"]he[/TD]
[TD="class: xl64"]down[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="class: xl64"]dog[/TD]
[TD="class: xl64"]her[/TD]
[TD="class: xl64"]did
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="class: xl64"]go[/TD]
[TD="class: xl64"]him[/TD]
[TD="class: xl64"]are[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="class: xl64"]is[/TD]
[TD="class: xl64"]his[/TD]
[TD="class: xl64"]have[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="class: xl64"]like[/TD]
[TD="class: xl64"]in[/TD]
[TD="class: xl64"]no[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="class: xl64"]she[/TD]
[TD="class: xl64"]little[/TD]
[TD="class: xl64"]one[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="class: xl64"]will[/TD]
[TD="class: xl64"]on[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="class: xl64"]too[/TD]
[TD="class: xl64"]play[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="class: xl64"]at[/TD]
[TD="class: xl64"]up[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="class: xl64"]can[/TD]
[TD="class: xl64"]with[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="class: xl64"]see[/TD]
[TD="class: xl64"]you[/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="class: xl64"]the[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="class: xl64"]was
[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]22[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]23[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]24[/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[TD="class: xl64"][/TD]
[/TR]
[TR]
[TD="align: center"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]26[/TD]
[TD][/TD]
[TD="class: xl67"]Random Word Selected:
[/TD]
[TD="class: xl66"][returned here]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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!
 
Upvote 0
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)
 
Upvote 0
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!
Click to expand...

Maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Group#​
[/td][td]
1​
[/td][td]
2​
[/td][td]
3​
[/td][td][/td][td]
Random Group#​
[/td][td]
Random Word​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
%​
[/td][td]
70%​
[/td][td]
20%​
[/td][td]
10%​
[/td][td][/td][td]
1​
[/td][td]
dog​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Group Name​
[/td][td]
RED​
[/td][td]
ORANGE​
[/td][td]
YELLOW​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
Words Count​
[/td][td]
18​
[/td][td]
16​
[/td][td]
11​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td][/td][td]
a​
[/td][td]
and​
[/td][td]
Cherry​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td][/td][td]
and​
[/td][td]
big​
[/td][td]
for​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td][/td][td]
be​
[/td][td]
but​
[/td][td]
yes​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td][/td][td]
blue​
[/td][td]
by​
[/td][td]
jump​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td][/td][td]
cat​
[/td][td]
has​
[/td][td]
get​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td][/td][td]
do​
[/td][td]
he​
[/td][td]
down​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td][/td][td]
dog​
[/td][td]
her​
[/td][td]
did​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td][/td][td]
go​
[/td][td]
him​
[/td][td]
are​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td][/td][td]
is​
[/td][td]
his​
[/td][td]
have​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td][/td][td]
like​
[/td][td]
in​
[/td][td]
no​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td][/td][td]
she​
[/td][td]
little​
[/td][td]
one​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td][/td][td]
will​
[/td][td]
on​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td][/td][td]
too​
[/td][td]
play​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td][/td][td]
at​
[/td][td]
up​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td][/td][td]
can​
[/td][td]
with​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td][/td][td]
see​
[/td][td]
you​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td][/td][td]
the​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
22​
[/td][td][/td][td]
was​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
23​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.
 
Upvote 0
Re: Need to generate a random number based on three columns of various probabilities

Or


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


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.
 
Last edited:
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

GomaPile
  • Question Question
Coaches Random Numbers
2
Replies
10
Views
481
Eric W
Eric W
P
  • Solved
Random predictions based on small number result (8 results predicted 125 numbers)
Replies
5
Views
140
Joe4
Joe4
M
  • Question Question
Can I combine randbetween, rept, and vlookup to get multiple answers?
Replies
4
Views
379
Marla89
M
pcc
  • Question Question
Can anyone explain this behaviour?
Replies
5
Views
345
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Question Question
Creating a random sequence of items from a weighted list with no duplicates
Replies
6
Views
115
shg
shg

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top