RAND Between

[indygo]

hi guys,

Two cells question with random numbers formula:

In A1 I have random number generator in a range from 1 to 99.
= RANDBETWEEN(1,99)

In B1 I want to add formula which would fluctuate values from A1 but within a certain range. So I have in B1 this formula:

=A1+RANDBETWEEN(-25,25)

That's the range I want B1 to move randomly. Now the problem is, I don't want B1 to go above or below the range of 1 to 99. If A1 generates random number let's say 5
and B1 generates -25 it will create -20. Now I want to avoid that.

Can we make b1 to be still random within that range but never exit range of 1-99?

I know I can make both cells random but they have to be related and move within a certain range.

thanks

 

[JoeMo]

Change the formula in A1 to:
=RANDBETWEEN(26,74)

 

[Eric W]

Try this in B1:

=RANDBETWEEN(MAX(1,A1-25),MIN(99,A1+25))

 

[Rick Rothstein]

I think I like JoeMo's solution better. I believe Eric's solution will over-represent the number 1 if the A1 contains numbers 1 through 25 and over represent the 99 if A1 contains numbers 75 through 99. For example, if A1 were to contain 99, then half the random numbers B1 would generate would produce a number of 99 whereas the other 25 numbers would produce 26 unique random numbers.

 

[Eric W]

Good point Rick, but I think your analysis is a little off. If A1 contains 99, then the B1 formula becomes

=RANDBETWEEN(74,99)

so the range is 25 instead of 50. 99 would not be chosen 50% of the time. So if the range is 74-99 it seems that 99 might be picked twice as often as 35, since 35 will only occur in a 50-number range. But, there are 50 ranges which contain 35, while there are only 25 ranges which contain 99. So it might seem that things even out. I could probably work out the math, given time. But I did write up a quick Monte Carlo simulation of the situation, and it seems that over time, using my formula, the values near the extremes actually are selected less often.

So JoeMo's formula eliminates 50 possible values from A1. My option keeps all options for A1, and it keeps all options for B1, but admittedly it skews the curve somewhat. I figured I'd give the options to the OP and let the OP decide which way to go.

 

[Rick Rothstein]

Good point Rick, but I think your analysis is a little off.

Admittedly, it was a top of the head analysis (I had some "chores" waiting for me that had to be attended to) that was off "just a little bit". In thinking about it, given the OP's restriction, I think some kind of skewed result would probably be inevitable no matter how you approached it.

 

[indygo]

Wow so many replies from forum legends thanks guys

Actually I'm using this for my montecarlo simulation. That's why A1 needs move freely between of 1-99.

I will go with Eric's advice.

PS.
It's counter-intuitive that MAX is describing the lowest value and MIN function the highest.

 

[Eric W]

In thinking about it, given the OP's restriction, I think some kind of skewed result would probably be inevitable no matter how you approached it.

That was pretty much my thought too. I graphed my results, and the curve looked like a circus tent, with peaks at 25 and 75. Not a flat line. But unless the OP has a specific need, that's probably as good as it will get.

Indygo: Glad it works for you. Your comment about the MAX/MIN functions being counter-intuitive I've heard before from other people, and I still have to think about it too. But just make sure you work through the logic to see why it's "right". Try values of 10, 50, and 90 for A1, and use the Evaluate formula tool.

 

[shg]

If you're doing a Monte Carlo simulation, it's critical that the distribution of numbers follow the distribution of numbers you are simulating. For example, the roll of a die can be accurately simulated by a random number uniformly distributed between 1 and 6, but the roll of multiple dice can't be simulated by uniform numbers between n and 6n. If your distribution doesn't follow the process you're simulating, the results would tell you anything of value.

 

[shg]

Typo:

... the results won't tell you anything of value.