If(randbetween)

[Reboshua]

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,"")>

 

[shg]

That would likely generate duplicates, would it not?

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

 

[Reboshua]

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

 

[shobolos]

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

 

[Reboshua]

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

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

 

[shg]

Doesn't that give duplicates?

 

[Reboshua]

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

 

[shg]

Well, okey-dokey then!