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.

Table of Contents

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.