To generate a single or a set of random odd or even numbers, at present, there is no specific function in Docs Sheets. Then how to generate odd or even random numbers in Google Sheets?
I am going to use a very simple combination formula for this purpose. It’s the combination of RANDBETWEEN, ROW, ODD/EVEN.
In the below example section, I will explain how to code this combination formula flawlessly.
Formula to Generate Odd or Even Random Numbers
Please note the following.
Once you have generated the required numbers, please copy that numbers and paste as values – Ctrl+Shift+v in Windows, and ⌘ + Shift + v in Mac – on another range. As the RANDBETWEEN involved in the combo is volatile.
As already stated above, I am going to use a combo formula. What else? The combo formula is an array formula which can generate a set of numbers!
Let’s start with random odd numbers.
Random Odd Numbers
To generate a random odd number between 1 and 999 (both the number inclusive), you can use this combo.
=ODD(RANDBETWEEN(1,999))To return a set of (here 4) odd numbers between 1 and 999, modify the above formula as below.
=ArrayFormula(ODD(RANDBETWEEN({1;1;1;1},999)))Must Check: Google Sheets Array Formula Example and Usage.
In the above formulas, the low end of the random odd number range is 1 and the high end of the random odd number range is 999.
You can change this range. But when you generate a large number odd random numbers, keep this range as wide as possible like 1 to 99999. This can avoid duplicates.
Also, in such case, the just above formula won’t be useful. Suppose you want to generate 20 odd random numbers. You must change the above low range array {1;1;1;1} manually. See that formula.
=ArrayFormula(ODD(RANDBETWEEN({1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1},1000)))To avoid this ‘problem’, here you can replace the low range array with a ROW formula as below.
=ArrayFormula(ODD(RANDBETWEEN(row(A1:A20)^1,1000)))
Random Even Numbers
In the above formula example, I have explained how to generate a set of random odd numbers in Google Sheets. Do you want random even numbers?
Replace the function ODD in the above formula with EVEN. That’s the only change required.
=ArrayFormula(EVEN(RANDBETWEEN(row(A1:A20)^1,1000)))
When you use the above formula without ODD or EVEN, it generates random numbers with the number of times specified by the row function. The ODD/EVEN function makes the difference.
I mean, the ODD function rounds up the even random numbers to odd and on the contrary, the EVEN function rounds up the odd random numbers to even.
That’s all. Hope you have enjoyed this post.
Additional Resources:
