In this article, we will show formula for finding odd or even numbers in Excel. We will create Excel formula with common functions like IF, MOD, ISEVEN and ISODD. Here, you will be able to identify if the number is even or odd. So, without any delay, let’s start the discussion.
Download Practice Workbook
To practice by yourself, download the following workbook.
4 Ideal Examples of Excel Formula for Finding Odd or Even Numbers
To demonstrate the method we will use the following dataset, consisting of the Name and Code No. column. Here, you can easily notice that Code No. column contains even and odd numbers. So, using this dataset, we will go through 4 easy examples of Excel formulas for finding Odd or Even numbers.
1. Utilize IF & ISEVEN Functions to Create Formula for Odd or Even Numbers
The ISEVEN function is used to identify even and odd numbers. This function returns TRUE if the number is even. Else, it returns FALSE. We can utilize the IF function with the ISEVEN function for creating an Excel formula for odd or even numbers. Let’s follow the steps below to learn the method.
STEPS:
- Firstly, we will another column beside the Code no. column.
- This newly formed Odd/Even column will contain the information if the adjacent cell contains an odd number or an even number.
- Now, we will write the formula.
- Therefore, select the D5 cell and write down the following formula:
=IF(ISEVEN(C5),"Even","Odd")
- After writing, press Enter to exit from the editing mode.
- As a result, you can see that the C5 cell contains an even number.
Here, the ISEVEN function returns TRUE if the C5 cell contains an even number. Else, it will return FALSE. And, the IF function returns Even if the ISEVEN function returns TRUE. Else, it will return Odd. As C5 contains 1234 and it is an even number. Therefore, the ISEVEN function returns TRUE and from there, the IF function returns Even.
- After that, drag down the formula in the D6:D14 range with Fill Handle.
- As a result, in the D column, you can see the outcome.
- If the adjacent cell is even, then it is showing Even in the DÂ column.
- And, if the adjacent cell is odd, then it is showing Odd in the DÂ column.
2. Make Formula Using IF & ISODD Functions to Check Odd or Even Numbers in Excel
The ISODD function is used to identify even and odd numbers. This function returns TRUE if the number is odd. Else, it returns FALSE. We can make a formula using the IF and ISODD function for creating an Excel formula for checking odd or even numbers. Let’s follow the steps below to learn the method.
STEPS:
- Firstly, we will write the formula.
- Therefore, select the D5 cell and write down the following formula:
=IF(ISODD(C5),"Odd","Even")
- After writing, press Enter to exit from the editing mode.
- As a result, you can see that the C5 cell contains an even number.
Here, the ISODD function returns TRUE if the C5 cell contains an odd number. Else, it will return FALSE. And, the IF function returns Even if the ISODD function returns FALSE. Else, it will return Odd. As C5 contains 1234 and it is an even number. Therefore, the ISODD function returns FALSE and from there, the IF function returns Even.
- After that, drag down the formula in the D6:D14 range with the Fill Handle option.
- As a result, in the D column, you can see the outcome.
- If the adjacent cell is even, then it is showing Even in the DÂ column.
- And, if the adjacent cell is odd, then it is showing Odd in the DÂ column.
Read More: How to Check If a Number Is Odd in Excel
3. Join Excel IF and MOD Functions to Build Formula for Odd or Even Numbers
The MOD function can be used to identify even and odd numbers. This function returns the remainder of two numbers after division. If we do the division of a number by 2, then the odd number will return 1 and an even number will return 0. We can insert this basic concept inside the IF function with the help of the MOD function. In this way, we can create a formula for determining odd or even numbers. Let’s follow the steps below to learn the method.
STEPS:
- To do so, select the D5 cell and write down the following formula:
=IF(MOD(C5,2)=0,"Even","Odd")
- After writing, press Enter to exit from the editing mode.
- As a result, you can see that the C5 cell contains an even number.
Here, the MOD(C5,2) formula returns the remainder of the C5 cell’s division by 2. For an even number, the result is 0 and for an odd number, it is 1. So, the IF function will show Even if the remainder is 0. Else, it will return Odd. As C5 contains 1234 and it is an even number. Therefore, the Mod function returns 0 and from there, the IF function returns Even.
- After that, drag down the formula in the D6:D14 range with the Fill Handle option.
- As a result, in the D column, you can see the outcome.
- Here, the D column is showing if the adjacent cell is even or odd.
Read More: How to Fill Odd Numbers in Excel
4. Combine FILTER and MOD Functions to Find Odd or Even Numbers in Excel
The FILTER function is used for filtering data dependent on some criteria. Using the MOD function as the criterion, we will filter even and odd numbers. Let’s follow the steps below to learn the method:
STEPS:
- Firstly, write the following formula in the D5 cell:
=FILTER($C$5:$C$14,MOD($C$5:$C$14,2)=1)
- After that, hit the Ctrl + Shift + Enter button.
- As a result, you will get all the odd numbers in the BÂ column.
Here, MOD($C$5:$C$15,2)=1Â returns the remainder after the numbers in C5:C15 are divided by 2 and then checks if the result equals 1. If the outcome is 1 then the condition is TRUE else it is FALSE. FILTER($B$5:$B$21,MOD($C$5:$C$15,2)=1)Â returns the value from the C5:C15 range when it finds the condition TRUE.
- Now, we need to find out the even numbers from the range.
- For this reason, write the following formula in the E5 cell:
=FILTER($C$5:$C$14,MOD($C$5:$C$14,2)=1)
- After that, press the Ctrl + Shift + Enter button.
- As a result, you will get all the even numbers of the BÂ column.
Here, MOD($C$5:$C$15,2)=0Â returns the remainder after the numbers in C5:C15 are divided by 2 and then checks if the result equals 0. If the outcome is 0 then the condition is TRUE else it is FALSE. FILTER($B$5:$B$21,MOD($C$5:$C$15,2)=0) Â returns the value from the C5:C15 range when it finds the condition TRUE.
- Finally, you can observe the odd and even numbers separately.
Read More: How to Separate Odd and Even Numbers in ExcelÂ
Conclusion
In this article, we have shown Excel formula for odd or even numbers. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Last but not least, please use the comment section below to post any questions or make any suggestions you might have.
Odd or Even Numbers: Knowledge Hub
- How to Determine If a Number Is Even in Excel
- How to Verify Odd and Even Using IF Function in Excel
- How to Sort Odd and Even Numbers in Excel
- Excel Conditional Formatting for Odd and Even Numbers and Rows
- How to Check If a Number Is Odd or Even with Excel VBA
<< Go Back to | Excel for Math | Learn Excel
