Excel probabilities

Question

Is there a way to assign probabilities to the possible outcomes of the randbetween formula function in Excel that doesn't involve a thousand-character formula?

Answer 1

Thank You! Very Helpful!

Answer 2

Not directly as random is random (so every number is equally probable). Can you give an example of what you are trying to do? The best option may be to use a IFS function to weight if there are small number of weightings, or a helper table if there are a large amount, but it depends on the problem you are trying to solve.

For example if there were three bands: 20% chance of number between 1-6, 60% chance of number between 7-8, and 20% chance of number between 9-10, then I'd use a formula like this:

=LET(rand, RAND(), IFS(rand<=0.2,RANDBETWEEN(1,6),rand<=0.8,RANDBETWEEN(7,8),TRUE,RANDBETWEEN(9,10)))

Or if you have more you'd have a helper table like this:

Rand_Max Start End

0.2 1 6

0.8 7 8

1.0 9 10

with a formula like this:

=LET(rand,RAND(),RANDBETWEEN(XLOOKUP(rand,Table1[Rand_Max],Table1[Start],0,1),XLOOKUP(rand,Table1[Rand_Max],Table1[End],0,1)))

Answer 3

Hello,

Greetings for the day!

RANDBETWEEN function - Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated.

The only syntax available with RANDBETWEEN function is the lower and higher range. There are no option to provide a pattern or probability.

Syntax
=RANDBETWEEN (bottom, top)
Arguments
bottom - An integer representing the lower value of the range.
top - An integer representing the upper value of the range.

Example - =RANDBETWEEN(1,100)
Random number between 1 and 100 (varies)

https://support.microsoft.com/en-us/office/randbetween-function-4cc7f0d1-87dc-4eb7-987f-a469ab381685

Hope this information would be useful to you. Please feel free to get back if you need any additional info.

Thank you!
Ravikumar
Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.