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.
Table of Contents
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:
- It takes the range B3:B10 as input
- It applies the ISODD function to each row of this range using BYROW and LAMBDA functions
- 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.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form