Are you looking to generate odd or even random numbers in Google Sheets? While there isn’t a specific function for this, I have a simple combination formula that does the trick. In this article, I’ll guide you through the process of using the combination of RANDBETWEEN, ROW, ODD/EVEN functions to generate these numbers flawlessly.
Formula to Generate Odd or Even Random Numbers
Before we begin, please note that after generating the required numbers, it’s important to copy and paste them as values (Ctrl+Shift+v in Windows, and ⌘ + Shift + v in Mac) on another range. This is because the RANDBETWEEN function used in the formula is volatile and will recalculate every time the sheet is refreshed.
Now, let’s dive into generating random odd numbers.
Random Odd Numbers
To generate a random odd number between 1 and 999 (both inclusive), you can use the following combo formula:
=ODD(RANDBETWEEN(1,999))If you want to generate a set of odd numbers (let’s say 4) between 1 and 999, modify the formula as follows:
=ArrayFormula(ODD(RANDBETWEEN({1;1;1;1},999)))You can adjust the range as per your requirements. However, when generating a large number of odd random numbers, it’s advisable to keep the range as wide as possible (e.g., 1 to 99999) to avoid duplicates. In such cases, manually modifying the low range array in the formula becomes cumbersome. To simplify this, you can replace the low range array with a ROW formula, as shown below:
=ArrayFormula(ODD(RANDBETWEEN(row(A1:A20)^1,1000)))
Random Even Numbers
If you’re interested in generating random even numbers, simply replace the ODD function in the formula with the EVEN function:
=ArrayFormula(EVEN(RANDBETWEEN(row(A1:A20)^1,1000)))
By using the ODD/EVEN functions, we can round up the random numbers to either odd or even. It’s that simple!
That’s all for now. I hope you found this post helpful. If you want to learn more about Google Sheets and explore additional resources, head over to Crawlan.com.
