How to use the ISODD function

What is the ISODD function?

The ISODD function returns TRUE if a cell contains an odd number, FALSE if even number. The ISODD and ISEVEN functions are different from the other IS functions, they return an error if the input value is an error. This is not the case with the other IS functions, they return TRUE or FALSE.

1. Introduction

Can the ISODD function handle error values?

No. Contrary to what you might expect the function doesn't return FALSE when it encounters an error value. Instead, it actually returns the error value itself.

Can the ISODD function handle cell ranges/arrays?

No, and this is weird as most Excel functions work with arrays. There is a workaround that I demonstrate below.

What is an odd number?

An odd number is an integer that is not evenly divisible by 2. Odd numbers have a remainder of 1 when divided by 2. For example: 5/2 = 2 with remainder 1. The set of odd natural numbers is {1, 3, 5, 7, 9...}. The opposite of an odd number is an even number.

What is an integer?

An integer is a whole number that can be positive, negative, or zero. Integers do not have decimal values. Examples of integers are -5, 12, 1, 15, 5005.

What is evenly divisible by 2?

A number that is evenly divisible by 2 can be divided by 2 without any remainder. The number divides perfectly into 2 equal parts. Examples of numbers evenly divisible by 2 are 12, 24, 800, 258. Even numbers are always evenly divisible by 2.

What is a remainder?

The remainder is what is left over after dividing two numbers. For example, 7 divided by 2 has a quotient of 3 and leaves a remainder of 1. 2*3 +1 = 7 When there is no number left over the remainder is 0. Evenly divisible numbers have a remainder of 0.

What is an even number?

An even number is perfectly divisible by 2. For example: 12/2 = 6 with remainder 0.

What is a natural number?

Natural numbers start from 1 and go on infinitely (1, 2, 3, 4, 5, 6, ...). They do not include negative integers or zero.

Other IS functions

Excel Function	Description
ISBLANK(value)	Returns TRUE if the value is empty, FALSE otherwise
ISERR(value)	Returns TRUE if the value is any error value except #N/A, FALSE otherwise
ISERROR(value)	Returns TRUE if the value is any error value, FALSE otherwise
ISEVEN(value)	Returns TRUE if the value is an even number, FALSE for odd numbers
ISFORMULA(reference)	Returns TRUE if the cell contains a formula, FALSE otherwise
ISLOGICAL(value)	Returns TRUE if the value is a logical value (TRUE/FALSE), FALSE otherwise
ISNA(value)	Returns TRUE if the value is the #N/A error, FALSE otherwise
ISNONTEXT(value)	Returns TRUE if the value is not text, FALSE if it is text
ISNUMBER(value)	Returns TRUE if the value is a number, FALSE otherwise
ISODD(value)	Returns TRUE if the value is an odd number, FALSE for even numbers

2. Syntax

ISODD(number)

number

Required. The value you want to check for an odd number.

The ISODD function is different from the other IS functions, it does not return FALSE for error values like #N/A, #REF, #VALUE etc.

3. Example 1

This section demonstrates the ISODD function processing several values in cell range B3:B9.

The first value is in cell B3, it contains 2 and the function returns FALSE in cell C3. 2 is an even number.

Formula in cell C3:

The second value is in cell B4, it contains 1 and the function returns TRUE in cell C4. 1 is an odd number.

The third value is in cell B5, it contains #DIV/0! and the function returns #DIV/0! in cell C5. #DIV/0! is an error value that the ISODD function can't handle in contrast to other IS functions.

The fourth value is in cell B6, it contains #N/A and the function returns #N/A in cell C6.

The fifth value is in cell B7, it contains #VALUE! and the function returns #VALUE! in cell C7.

The sixth value is in cell B8, it contains #NAME! and the function returns #NAME! in cell C8.

The seventh value is in cell B9, it contains #REF! and the function returns #REF! in cell C9.

4. Example 2

This image above shows a worksheet with a formula that filters odd numbers from a given range of values. In column B, we see a list of values: 264, 30, 909, 542, 248, 752, 331, and 991

Formula in cell D3:

This formula does the following:

1. It takes the range B3:B10 as input
2. It applies the ISODD function to each row of this range using BYROW and LAMBDA functions
3. It then filters the original range, keeping only the values where ISODD returned TRUE

The result of this formula is shown in cells D3:D5: 909, 331, and 991. These are indeed all the odd numbers from the original list in column B.

Explaining formula

Step 1 - Identify odd numbers

Variable a will be defined in step 2 by the LAMBDA function, it will contain values from each row.

ISODD(a)

The ISODD function returns TRUE or FALSE based on the number being odd or not respectively.

Step 2 - Define a variable in the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a, ISODD(a))

The LAMBDA function is needed for the BYROW function to work properly.

Step 3 - Pass single values to the LAMBDA function

The BYROW function puts values from an array into a LAMBDA function row-wise.

Function syntax: BYROW(array, lambda(array, calculation))

BYROW(B3:B10,LAMBDA(a,ISODD(a)))

The BYROW function will pass each value to the LAMBDA function, row-wise is important since the cell reference is only one column wide. This will allow the ISODD function to process each number one by one.

Step 4 - Filter odd numbers

FILTER(B3:B10,BYROW(B3:B10,LAMBDA(a,ISODD(a))))

The FILTER function filters out all even numbers returning only odd numbers.

Functions in 'Information' category

The ISODD function function is one of 19 functions in the 'Information' category.

Excel function categories

Excel categories

Leave a Comment