Excel EVEN Function
In this tutorial, we will discover how to use Excel EVEN Function. EVEN function is a mathematical function which rounds off the given number to the nearest next even integer. EVEN function takes a number as argument and returns an even number in the cell.
What is Excel EVEN Function?
The Excel EVEN function rounds a positive number up and negative number down to the nearest even integer.
The Excel EVEN function returns a numeric value by rounding the specified number to the next even integer. EVEN function always rounds numbers away from zero. Therefore, the positive numbers become larger and negative numbers become smaller (i.e., more negative). This function takes just one parameter (number), which should be a numeric value. If you pass non-numeric in its argument, it will throw a #VALUE! error.
Syntax
Parameters
Number (required): This parameter represents the cell which you want to convert to the nearest even integer.
Return
- If you pass positive number in its argument, EVEN function returns a positive even value by rounding off the given number up to the next even integer.
- If you pass positive number in its argument, EVEN function returns a negative even value by rounding off the given number down away from zero to the next negative integer.
- If you pass zero (0) and numbers that are already even integers in its argument, number is unchanged.
- With non-numeric values, EVEN returns a #VALUE! error.
Points to Remember about EVEN Function
- The EVEN function only works with numeric values.
- The EVEN function returns the nearest next even integer for any specified decimal number.
- The function returns the number away from zero.
- The EVEN function only works for numeric values. If you supply any non-numeric value in its parameter, it will throw #VALUE! error.
- If you pass zero (0) and numbers that are already even integers in its argument, no rounding occurs.
Example
Example 1: Use EVEN function to round off the number on the following examples.
Follow the below steps to round off the numbers to its nearest even number:
STEP 1: Insert a helper column
Add a column next to “Numbers” and type the column name “Even Round off” on top of the cell.
It will look similar to the below image:
In the helper column, we will type the EVEN function for every row and find round off the numbers to its nearest even number.
NOTE: As the above image shows, we have formatted the column with borders and font to make the worksheet more visually attractive.
STEP 2: Insert the EVEN formula
The next step is to enter the formula so put your cursor in the second row of your helper column start typing: = EVEN(
It will look similar to the below image:
STEP 3: Add the “Number” parameter
The first parameter includes “number,” which represents the cell you want to convert to the nearest even integer. Here, cell reference B5 holds our Text1. So our formula becomes: =EVEN (B5)
It will look similar to the below image:
STEP 4: EVEN will return the even integer number
EVEN (B5) will return the even integer number after rounding it off to its nearest even number. The nearest even number to 45.3976106 is 46 and this function has returned the same output.
STEP 5: Drag the formula to other rows to repeat
Put your cursor on the formula cell and take it towards the right of the rectangular box. You will notice that the cursor will change into the ‘+’ icon.
It will look similar to the below image:
Drag the + icons to all the cells below it. This will automatically copy the EVEN function to all the cells. As a result, EVEN will return the integer numbers after rounding it off to its nearest even number.
You will have the following outputs for each number:
STEP 6: Explanation of each Output
- The first number in the above table was 45.3976106. When we applied the EVEN function for this number, it returned its nearest EVEN integer value, i.e., 46. The EVEN function always rounds numbers away from zero, so the positive values become larger, and therefore it returned 46.
- For the second number, a rounded negative even number for -50.6546265 is 52. The EVEN function always rounds numbers away from zero, so the negative numbers become smaller, and therefore it returned 52.
- For the third number, the rounded even number for 85.4139093 is 86.
- The same applies for the fourth value, and the even rounded number for 31.5893471 is 32.
Example 2: In the below table, find all the even numbers using the Excel EVEN Function and count how many EVEN numbers are there in the list.
To find the count of even numbers from the above list, we will use a combination of Excel EVEN and COUNTIF function. Follow the below given steps to achieve the same:
STEP 1: Insert two helper columns
- Add a column next to “Numbers” and type the column name “Even” on top of the cell.
- Add another helper column and name it “Total Even number.”
It will look similar to the below image:
- In the first helper column, we will apply the EVEN function and check for each given number, whether is it is EVEN or ODD.
- In the second helper column, we will apply the COUNTIF function and count the EVEN numbers present in our list.
STEP 2: Insert the formula = cell reference
The next step is to enter the formula. Start the formula with the = (sign), and then we will pass the reference of our data cell. In our case, our data is in cell B5, so our formula becomes: =B5
It will look similar to the below image:
STEP 3: Insert the EVEN formula
- We will compare the above formula with the output of the EVEN function. Type the ‘=’ sign and enter the formula by typing: = B5 = EVEN(
It will look similar to the below image:
- Include the first parameter “Text1” which represents the cell which you want to convert to the nearest even integer. Here, cell reference B4 holds our Text1. So our formula becomes: = B5 = EVEN(B5)
It will look similar to the below image:
The above formula will check whether the value in cell B5 is equal to the closest EVEN number. If it is equal, it will return TRUE; if it is not equal, it will return FALSE.
STEP 4: EVEN will return the even integer number
The combination of above formula will return a Boolean value False since A2 (11.11) and EVEN (11.11) are not equal.
STEP 5: Drag the formula to other rows to repeat
- Put your cursor on the formula cell and take it towards the right of the rectangular box. You will notice that the cursor will change into the ‘+’ icon.
- Drag the + icons to all the cells below it. This will automatically copy the EVEN function to all the cells. As a result, EVEN will return the integer numbers after rounding it off to its nearest even number.
You will have the following Boolean outputs:
STEP 6: Apply COUNTIF formula to get the total count of even numbers
Apply the COUNTIF formula in cell E6 to get the total count of TRUE, which is representing EVEN numbers in the list. So we will use the following formula:
=COUNTIF (C5: C14, “TRUE”)
Refer to the below image:
STEP 7: COUNTIF will return the total number of even numbers
Here COUNTIF formula counts all the TRUE values in the range C5: C14 and return the total count of all the TRUE values. The total number of TRUE values in the range C5: C14 is 3. That means there are 3 EVEN numbers are there in the list.
Example 3: In the below table we have some data inputs s text, let’s see what happens when we use the Excel EVEN Function.
Follow the below given steps to achieve the same:
STEP 1: Insert a helper column
Add a column next to “Items” and type the column name “Even Number” on top of the cell.
It will look similar to the below image:
In the helper column, we will check for each given number whether is it is EVEN or ODD.
STEP 2: Insert the EVEN formula
- Enter the formula so put your cursor in the second row of your helper column start typing: = EVEN(
- Include the parameter “Number” which represents the cell which you want to convert to the nearest even integer. Here, cell reference B4 holds our value. So our formula becomes: = EVEN(B5)
It will look similar to the below image:
STEP 3: EVEN will return an error
The EVEN function only works for numeric values. If you supply any non-numeric value in its parameter, it will throw an #VALUE! error. In the B5 cell, we have supplied string values. Therefore the EVEN (B5) formula has returned the #VALUE! error as shown in the below image:
When you drag the formula for the rest of the cells, you will find the same #VALUE! error because all the values are non-numeric.
