# If(randbetween)

#### Reboshua

##### Board Regular
I am trying to figure out how to use the IF(RANDBETWEEN) function to generate a random number, and once that number is generated to check that number against a range of possible outcomes, each of which has another variable associated with it. See this:

 Row\Col A​ B​ C​ D​ E​ 1​ Team RandBetween Formula Range Low​ Range High​ Name​ 2​ Player 1 1​ 32​ Jane​ 3​ Player 2 33​ 50​ John​ 4​ Player 3 51​ 62​ Sally​ 5​ Player 4 63​ 72​ Billy​ 6​ Player 5 73​ 80​ Bobby​ 7​ Player 6 81​ 88​ Cole​ 8​ Player 7 89​ 92​ Shane​ 9​ Player 8 93​ 96​ Ciara​ 10​ Player 9 97​ 98​ Beth​ 11​ Player 10 99​ 100​ Sam​

<tbody>
</tbody>

In this example, I would use the cells in column B to use an IF(RANDBETWEEN(1,100) statement (probably 10 nested ones). So, in cell B2, if the random number generated in the first IF statement was 54, the value returned in cell B2 would be Sally.

I started writing it out as =IF(AND(RANDBETWEEN(1,100)>=C2,<d2),e2,"") but="" got="" errors="" when="" doing="" this="" and="" didn't="" know="" why.=""

Thoughts?

Thank you!</d2),e2,"")>

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
That would likely generate duplicates, would it not?

Are you just trying to randomize the order of players, or something more esoteric?

If you nest the IF(RANDBETWEEN) statements to include all of the possible outcomes - aka c2,d2, c3,d3, c4,d4, etc., it should cover every possible outcome for that range and deliver one of the 10 names. I'm trying to create a weighted approach to choosing the names where I've specified the weights elsewhere in my spreadsheet. The low and high ends of the range are simply a way to articulate the weightings I've already chosen. In this example, I've already decided that I want Jane to have a 32% chance of getting chosen, John 18% and so forth. Does that make sense?

Thanks

=LOOKUP(RandBetween(1,100),\$C\$2:\$C\$11,\$E\$2:\$E\$11)

"=LOOKUP(RandBetween(1,100),\$C\$2:\$C\$11,\$E\$2:\$E\$11)"

What sort of witchcraft is this! Yes, this worked perfectly. Thank you

Doesn't that give duplicates?

It does, but that's ok. I don't need the results to be mutually exclusive.

Well, okey-dokey then!

Replies
5
Views
223
Replies
4
Views
448
Replies
9
Views
172
Replies
4
Views
189
Replies
3
Views
252

1,196,049
Messages
6,013,107
Members
441,748
Latest member
MrBigglesworth

### 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.

### Which adblocker are you using?

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

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