Generate Odd or Even Random Numbers in Google Sheets

Depending on your needs, you can use one of the following methods to generate odd random numbers or even random numbers in Google Sheets.

The first approach returns a random number between two specified values and converts it to either an odd or even number as needed. This method can return multiple numbers but may include duplicates when the range is narrow.

The second approach generates a sequence of odd or even numbers between a start and end value, then randomizes them and returns one, all, or a specified number of values. This method is more precise but is resource-intensive and may not work efficiently for very large ranges due to sequence generation constraints.

We will explore both approaches below.

Generating Odd or Even Random Numbers Using Approach 1

Let’s define the range using two input cells:

  • B1: Start value (the low end of the random range)
  • B2: End value (the high end of the random range)

For example, enter 1 in cell B1 and 100 in cell B2.

The following formula will generate an odd random number between these values:

=ODD(RANDBETWEEN(B1, B2))

If the formula returns a number greater than the specified end value, refresh it by copying, deleting, and pasting it again. This will generate a new number.

To generate multiple numbers (e.g., five numbers), use the following formula:

=ArrayFormula(ODD(RANDBETWEEN(SEQUENCE(5, 1, B1, 0), B2)))

You can replace 5 with 10 to generate 10 odd random numbers within the specified range.

To generate even random numbers, replace ODD with EVEN in the formulas:

=ArrayFormula(EVEN(RANDBETWEEN(SEQUENCE(5, 1, B1, 0), B2)))

Important Notes

  • Use this formula with wider ranges to minimize duplicates.
  • Occasionally, a number slightly outside the specified range may appear. Simply refresh the formula if needed.

Formula Explanation (Approach 1)

  • SEQUENCE(5, 1, B1, 0): Creates a sequence of 5 instances of the start number (B1).
  • RANDBETWEEN(SEQUENCE(...), B2): Generates a random number for each instance within the range.
  • ODD(...): Ensures all random numbers are odd.
  • ArrayFormula(...): Ensures the formula returns an array of values instead of just one.

Generating Odd or Even Random Numbers Using Approach 2

This method offers an accurate way to generate odd or even random numbers in Google Sheets without duplicates.

Formula to Generate Odd Random Numbers

=LET(start, B1, end, B2, start_odd, ODD(start), end_odd, IF(ISODD(end), end, end-1), n, (end_odd-start_odd)/2+1, numbers, SEQUENCE(n, 1, start_odd, 2), SORTN(numbers, 1, 0, RANDARRAY(ROWS(numbers)), 1))

This formula:

  • Assumes the range start (low end of the random range) is in B1 and the range end (high end of the random range) is in B2.
  • Returns one random odd number within the range.

To generate five odd numbers, modify the last part of the formula:

SORTN(numbers, 5, 0, RANDARRAY(ROWS(numbers)), 1)

To return all odd numbers within the range, replace 1 with ROWS(numbers).

SORTN(numbers, ROWS(numbers), 0, RANDARRAY(ROWS(numbers)), 1)
Generating all odd random numbers within a specified range in Google Sheets

Formula to Generate Even Random Numbers

=LET(start, B1, end, B2, start_even, EVEN(start), end_even, IF(ISEVEN(end), end, end-1), n, (end_even-start_even)/2+1, numbers, SEQUENCE(n, 1, start_even, 2), SORTN(numbers, 1, 0, RANDARRAY(ROWS(numbers)), 1))

This works exactly like the odd number formula, but it ensures that only even numbers are generated.

Key Benefits of This Approach

  • No duplicates
  • Ensures numbers stay within the specified range
  • Suitable for moderate ranges (e.g., 1-2,000 or 25,000-28,000)

However, performance may degrade if the range is extremely large.

Formula Explanation (Approach 2)

Let’s break down the even number formula as an example (the odd number formula follows the same logic):

  • start -> The lower limit (B1).
  • end -> The upper limit (B2).
  • start_even = EVEN(start) -> Converts the start number to an even number.
  • end_even = IF(ISEVEN(end), end, end-1) -> Ensures the end number is even.
  • n = (end_even - start_even) / 2 + 1 -> Calculates how many even numbers exist in the range.
  • numbers = SEQUENCE(n, 1, start_even, 2) -> Generates a sequence of even numbers.
  • SORTN(numbers, 1, 0, RANDARRAY(ROWS(numbers)), 1) -> Randomly selects one number from the sequence.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.