Excel Formula for Finding Odd or Even Numbers

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.

excel formula for 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.

Utilize IF & ISEVEN Functions to Create Formula for Odd or Even Numbers

  • 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 column.
  • And, if the adjacent cell is odd, then it is showing Odd in the column.

Read More: How to Verify Odd and Even Using IF Function in Excel


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.

Make Formula Using IF & ISODD Functions to Check Odd or Even Numbers in Excel

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 column.
  • And, if the adjacent cell is odd, then it is showing Odd in the column.

Read More: How to Check If a Number Is Odd in Excel (3 Easy Ways)


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.

Join Excel IF and MOD Functions to Build Formula for Odd or Even Numbers

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 (5 Suitable Examples)


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 column.

Combine FILTER and MOD Functions to Find Odd or Even Numbers in Excel

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 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 (6 Handy Ways)


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.


Related Articles

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo