ISODD Function
Evaluates a number as TRUE when it is an odd number and FALSE when it is an even number.
The ISODD function in Excel evaluates a number as TRUE when it is an odd number and FALSE when it is an even number.
When you have just a few numbers in the spreadsheet, it is easier to determine whether the number is odd or even.
But what if there were hundreds or thousands of such numbers? There would be no point in going through all the numerical values and assigning them their respective status since it would waste valuable time.
What's the next alternative, then?
It's none other than the ISODD function! The function will check whether a numerical value is even or odd and return the result as FALSE and TRUE, respectively.
However, I don't think that's all we could do with the function. The ever-reliable IF function works well with this function and helps us input the multiple conditions based on odd numbers.
Enough praises for the function! Let's get started and see how the function works, understand the syntax and see a couple of examples.
Understanding the function
The function is categorized as an Information function that returns TRUE for odd numbers by simply referencing or hardcoding the numerical values.
The simple purpose of the functions is to check whether the numbers referenced are odd or even.
For example, suppose you have three numbers 273, 268, and 172. If you use the function on these three numbers, you will get the result as TRUE, FALSE, and FALSE, respectively.
The function even accepts numerical values returned based on logical formulas. For example, if the formula is =IF(25>30,25,30), the result would either be 25 or 30. You can evaluate the number as TRUE or FALSE if you reference the result cell directly in the function.
However, when you reference a non-numeric value such as a 'text string,' the function determines it to be unacceptable and returns a #VALUE! Error.
The syntax for the function is:
=ISODD(number)
where,
number - (required) the reference to the numerical value which we want to evaluate
You can efficiently deal with odd numbers using the function in Excel.
How to use the function
Many beginners usually have this question - How do we use the function? And even though it is not rocket science, we are here to help you.
A function can be used from the library or as a worksheet formula. Most analysts/Investment bankers usually prefer to use them as a worksheet formula since it gives them more control and flexibility in reaching 'ideal' solutions.
Method #1: From the function's library
Using the function from the library has its advantages. If you are a beginner who has just started your journey to become an Excel wizard, the dialog boxes and the description will help you understand the function's purpose immensely.
To use the function from the library, please follow the steps below:
1. Select the cell in which you intend to get the result.
2. Click on the Formulas > More Functions > Information > select the ISODD function from the drop-down menu.
3. Selecting the function will open the dialog box as illustrated below:
4. Here, you can directly input the numerical value you need to evaluate or reference the cell with the particular numerical value.
Once you input the numerical view, you will get a preview of the result:
5. You might be aware of Excel's alternate interpretation of boolean values where TRUE is equal to 1 while FALSE is equal to 0. Here, we see the same scenario.
6. However, once you click on Ok, the function will return the result as a boolean value, i.e., TRUE.
Method #2: As a worksheet formula
We feel that using the function as a worksheet formula is better than the two methods. You will agree with us once you exponentially progress in using different functions together.
To use the function as a worksheet formula, you begin with an equal sign, type in the function name, and input the argument inside the parentheses.
Suppose you have the data as illustrated below:
To evaluate whether the numbers are odd, we will use the formula =ISODD(B3) in cell C3 and drag it down up to cell C6, which gives us the result:
Two of the four numbers are odd, i.e., 1 and 7, while the other two that evaluate to FALSE are the even numbers.
Example
We believe that you have a general idea about what the function does. Next, we will see examples based on different scenarios and what result the function would return.
The different scenarios apart from using the function on numbers are:
a. Dates
Dates are represented as serial numbers in Excel beginning from 1st January 1900, equal to 1. For example, suppose the date today is 18th August 2022; the equivalent serial number would be 44791.
Since these serial numbers are all natural numbers, the function has the same effect on date values as the numerical ones.
Suppose you have some dates in Excel, as illustrated below:
By using the formula as =ISODD(B3) in cell C3 and dragging it down to cell C6, we get the result:
To better understand what went behind the scene, we will use the magic keys of Ctrl + ~, which shows us the serial number for respective dates:
Whenever the serial number is even, we get the result as FALSE, while if the serial number is odd, then the function evaluates to TRUE.
NOTE
We have PasteSpecial the values in column C before using the Ctrl + ~ keys to view the serial numbers. Instead, if we had not, we would see the formulas to derive the result, i.e., =ISODD(B3).
b. Decimal Numbers
The function has a unique effect on decimal numbers. When you reference the decimal numbers as an argument inside the function, the numbers after the decimal point gets 'truncated.'
Truncate is an executive term when the digits after the decimal point are ignored.
So, if you have the number 27.34651658979154, the function would ignore all the digits after the decimal point and return the result for the number before the decimal point, i.e., 27.
Suppose you have some decimal numbers, as illustrated below:
When you use the formula =ISODD(B3) in cell C3 and drag it down, you get the result:
The rest of the numbers are evaluated similarly, giving the result TRUE or FALSE.
c. Text Strings and Errors
We mentioned before that when you input text strings as arguments, you would get an error in Excel. Well, time to check if it happens.
Suppose that the data in Excel as:
After using the formula =ISODD(B3), all we get is a bunch of errors in the spreadsheet.
An error would ultimately return an error, while on the other hand, all the text strings return a #VALUE! Error.
ISODD, along with other functions
Some functions in Excel, such as IF, OR, AND, etc., collaborate well with other functions and further help improve the user's work efficiency.
a. IF function
IF function allows for many possibilities with what you can do with odd numbers. If odd numbers are detected, the function will enable you to return customized text strings, numbers, or boolean values.
Suppose that you have the data, as illustrated below:
Here, we will use the formula =IF(ISODD(B3), "Odd number","Even number"), which gives us the result:
Then, we nest the function inside the logical IF statements, which further evaluates the TRUE or FALSE results to return customized text strings such as 'Even number' or 'Odd number.'
b. ROW function
The ROW function in Excel returns the row number for the referenced cell or range of cells.
The situation would rarely arise where you need to input particular values in the odd-numbered rows in Excel.
However, if something like that was to happen, we can use the formula =IF(ISODD(ROW(Sheet1!C3:C6)), "Odd Row"," "), which adds the text string 'Odd Row' whenever the formula detects an odd-numbered row.
Still, how do we make this combination more useful?
You can use a similar formula in the conditional formatting tool to highlight the odd-numbered rows in Excel.
First, we will select the cells upon which we want the formula to work.
To open the conditional formatting tool, use the keyboard key shortcut Alt + H + L + N and click on 'Use a formula to determine which cells to format.'
Here, we will use the formula =ISODD(ROW(B1:B8) and select the appropriate color to fill those resultant cells.
After clicking on Ok, we get the result as illustrated below:
ISODD vs. ISEVEN
The ISEVEN function is categorized as an Information function that works exactly opposite to ISODD, i.e., it evaluates to TRUE if the number is even and FALSE if the number is odd.
Using the function, you can quickly identify all the even numbers in the spreadsheet and perhaps even use the IF statement to run criteria based on those even numbers.
The syntax for the function is:
=ISEVEN(number)
where,
number - (required) the reference to the numerical value which we want to evaluate
Suppose you have some numbers in the spreadsheet, as illustrated below:
To evaluate these numbers, we will use the formula =ISEVEN(B3) in cell C3 and drag it down to cell C6, which gives us the result:
The function evaluates 2 and 10 as even out of the four numbers. Hence we get the result as TRUE and identify 1 and 7 as odd numbers.
You can try out the conditional formatting tool, the ROW function, and the ISEVEN function to notice how different your results are.
Bonus Content: VBA Code to evaluate Odd numbers
Many people perceive that macros are complicated to make if you do not have a coding background. However, that is wrong.
With some practice, anyone can write VBA codes to automate the workflow and improve work efficiency.
The VBA code below identifies whether the number you input in the spreadsheet is an even or an odd number.
Now, all you need to do is input numbers in column A of Sheet1. The macro automatically captures the number and evaluates if it is an odd number.
The question is, do you create a new module to create the macro? No, you select the sheet where you automatically want the numbers to be evaluated, i.e., Sheet1.
Next, select 'Worksheet' instead of General in the VBA code writer window and 'SelectionChange' from the adjoining drop-down.
If you see the beginning of the code as 'Private Sub Worksheet_SelectionChange (ByVal Target as Range),' you are on the right path.
Finally, you must type in the code in the window, save the workbook as Macro Enabled Workbook(just for the safe side) and return to Sheet1.
Type in different numbers and see what result you get for those numbers!
Key Takeaways
- The function evaluates whether the referenced number is odd and returns the result as TRUE, whereas if it is even, the result returns as FALSE.
- You can combine the function with IF statements that will return one result if the number is odd and another if the number is even.
- The function will return a #VALUE! Error if you input a non-numeric value as an argument.
- Suppose you input decimal numbers, then all the digits after the decimal are ignored. Instead, the function only evaluates the number before the decimal, called 'truncation.'
- Dates are represented as serial numbers; hence, the function can return whether the date is odd or even.
- The function that works exactly opposite to ISODD is the ISEVEN number that evaluates even numbers as TRUE.
or Want to Sign up with your social account?