Adding range in INDEX and RANDBETWEEN

Question

Working formula:

=INDEX({249,749,1999,4999,9999,10000,19999,50000},RANDBETWEEN(1,COUNTA(249,749,1999,4999,9999,10000,19999,50000)))

Amendment needed

249,749,1999,4999,9999,10000,19999,50000 are fixed INDEX and can be easily RANDBETWEEN.

I want to add a range of 500 to 1500 in INDEX and RANDBETWEEN.

Is there any way to include the range of 500 to 1500 in this formula? If not then what is correct way to create a formula as per my needs.

Answer 1

=IF(Randbetween(1,1009)<=1001,randbetween(500,1500),INDEX({249,749,1999,4999,9999,10000,19999,50000},randbetween(1,8)))

A random integer is determined between 1 and the number of possible numbers. if it is less than or equal to the number in the sequential range 500 to 1500, which is 1001, then we tell then we use the randbetween (500,1500). If the initial number if greater than our range, then we have it pull a random number from our supplied list of numbers. 8 is the number of supplied numbers in the static array.

Now if I recall correctly, randbetween is a volatile function. As such anytime something in your work book changes, this formula will recalculate and provide a new number.

UPDATE:

Since you want to limit the 500-1500 range to 35% of the time, try using either of the formulas below:

=IF(Randbetween(1,100)<=35,randbetween(500,1500),INDEX({249,749,1999,4999,9999,10000,19999,50000},randbetween(1,8)))

=IF(Rand()<=0.35,randbetween(500,1500),INDEX({249,749,1999,4999,9999,10000,19999,50000},randbetween(1,8)))

Answer 2

Here is one way.

In an empty column, type your numbers 249,1999,4999,9999,10000,19999,50000. I have removed 749 as it falls in the range 500-1500. Next type 500, 501 and then drag it down to 1500. Let's say you have them in Col A. You can also do this in a separate worksheet.

So if you start at A1 then the values will go up to row 1008 as shown in the image below.

Now use this formula

=INDEX(A1:A1008,RANDBETWEEN(1,1008))