Randbetween return only odd numbers

Question

I need to create a list of random numbers between two bounds in Excel. The trick is the numbers need to be odd. So I can't just use randbetween(). Should I just use something like =if(a1 % 2 =0) then a1=a1+1. I don't know the syntax for that in Excel but what I'm getting at is if the number is even (mod 2 =0) then add one.

Answer 1

I know I am in the game late, but wouldn't this be just as simple.

A1 = randbetween(0,100)
B1 = if(mod(A1,2)=0,A1+1,A1)

Answer 2

Use a little trick. CEILING and FLOOR have a second parameter which is what you need.

=CEILING(RANDBETWEEN(...),2)-1

Answer 3

You could also use RAND() here to be compatible with earlier excel versions, generically this formula will give you an even distribution, assuming C2 contains the lower bound and D2 the upper (both odd numbers)

=INT(RAND()*((D$2-C$2)/2+1))*2+C$2

Answer 4

You could get a random number n with a different range, and calculate 2*n+1 for your cell value. I presume lower bound is 0 (or 1), so your new range starts off at 0 as well (if not, see how you calculate the upper bound). For the upper bound, if you want m as your largest odd integer, then your upper bound is (obviously) (m-1)/2.

As an example, say you want odd numbers between 15 and 27. Your lower bound for randbetween will be (15-1)/2 = 7, and upper bound will be (27-1)/2 = 13. So, the cell formula will be =2*randbetween(7, 13)+1.