EVEN Function in excel as the name suggests it rounds any given number to its even value, the number can be both negative or positive, so for example if the given input is =EVEN(-1.5) the rounded result will be -2, and if the given input is =EVEN(1.5) then the result is 2 this is an inbuilt function in excel and the keyword to use this function is =EVEN( with a number provided as an argument.
Excel EVEN Function (Table of Contents)
- Even Function
- EVEN Formula in Excel
- What is an EVEN Function?
- EVEN Excel Function – Illustration
- How to Use?
Even Function in Excel
The EVEN Function is a Math and Trigonometry function and is used to round up a number to its nearest even integer.
EVEN Formula in Excel
Explanation of EVEN Excel Function
The Formula of EVEN in Microsoft Excel is given as:
EVEN( number )
Arguments used in EVEN Formula:
number: A numeric value to round up to an even integer.
The number can be a numeric value or a cell reference containing a numeric value.
What is an EVEN Function in EXCEL?
The EVEN Excel Function returns an even integer value, which is always rounded away from zero. So, if the number is positive, it returns the higher closest even value, and if the number is negative, the function returns the lower closest even value.
A numeric value can be directly inserted into the function with/without double quotes.
EVEN Excel Function – Illustration
Suppose you want to convert 1.2 to an even number. The number is given in B3.
The EVEN Formula to convert this number will be:
This will return 2.
You may alternatively use the number directly instead of giving the cell reference as shown in the following EVEN Formula:
You may also use the number in quotes. The function will recognize the number provided in the quotes and return the value. The syntax
will also return 2.
If you use a non-numeric value in the function, such as EVEN(“a”)
it will give an error #VALUE!
How to Use EVEN Function in Excel?
EVEN in Excel is very simple and easy to use. Let understand the working of EVEN in excel by some EVEN Function examples.
The function is used to obtain the nearest even value away from zero and is a very useful function. It removes the least significant digits from a number, and simplify while keeping close to the original value. It is used in the processing of items that comes in a multiple of twos. For EVEN Function example,
- the number of houses in a street having houses on both sides.
- Room allotment in a hostel in twin sharing
- Number of desks in a classroom when a single desk accommodates two students.
- A packing crate which packs two items at a time.
- Butterfly wings
- Flight numbers: eastbound or northbound
EVEN Function Example #1
Suppose you have a list of numbers given in B3:B9.
Now you want to convert these numbers to even numbers. The syntax for the first number will be given as:
Similarly, you can drag it for rest of the cells-C4:C9 to get the output for the remaining ones.
In the above EVEN Function example, you learned how to find an even number closest to the given number, away from zero. For example, a number 4.1 is closest to the even number 4, however, the EVEN Excel will return 6. Sometimes, you may want to find the closest even number to a given number, be it higher or lower. Let us see an EVEN Function in excel example of how to find the closest even number.
EVEN Function Example #2
In this EVEN function example, suppose you have a list of numbers in B3:B6 for which you want to find the closest even number.
You may use the following EVEN Formula for the first number 2.2 given in B3:
=IF(EVEN(B3) –B3 > 1, EVEN(B3) -2, EVEN(B3))
B3 = 2.2 here and its nearest even number is 2. EVEN(B3) will return 4 which is 2 greater than its actual nearest even number. Let us see the function in detail:
=IF(EVEN(B3) –B3 > 1, EVEN(B3) -2, EVEN(B3))
IF(EVEN(B3) –B3 > 1, if_true, if_false)
If EVEN(B3) is not the nearest even number of B3, then EVEN(B3) –B3 will be greater than 1.
if_true : EVEN(B3) -2
If EVEN(B3) –B3 is greater than 1, it will subtract 2 to give the nearest even value, else return EVEN(B3).
However, this EVEN Formula will not work with negative numbers. Suppose you have some negative numbers in cells B8:B10.
For negative number (in B8), the following EVEN Formula may be used:
=IF(ABS(EVEN(B8) – B8) > 1, IF(B8<0, EVEN(B8) +2, EVEN(B8) -2), EVEN(B8))
This will work for both negative and positive numbers. The main logic behind the EVEN Formula is:
If EVEN(B8) is not the nearest even number of B8, then EVEN(B8) ± 2 will be the nearest even number.
In this EVEN Formula,
ABS(EVEN(B8) – B8)
will return a positive value even if the difference is negative.
IF(ABS(EVEN(B8) – B8) > 1, if_true, if_false)
If |EVEN(B8) – B8| > 1, it means that EVEN(B8) is not the nearest even number.
if_true: IF(B8<0, EVEN(B8) +2, EVEN(B8) -2)
If |EVEN(B8) – B8| > 1, then
- if the number in B8 is positive, it should subtract 2 from EVEN(B8), which will return the nearest even value.
- if the number in B8 is negative, it should add 2 to EVEN(B8) which will return the nearest even value.
Else, EVEN(B8) is the nearest even value and should return as it is.
EVEN Function Example #3
In this EVEN function example, suppose you have a list of numbers and you want to find out which numbers are even and which are odd.
You may use the following EVEN Formula:
=IF(EVEN(B3) = B3, “Even”, “Odd”)
It will return “Odd”. Similarly, you can drag it to the rest of the cells.
This can be alternatively done by using the ISEVEN in excel. This function returns TRUE and FALSE statement
EVEN Function Example #4
In this EVEN function example, suppose you have a list of items needed to be packed. Similar items will be packed together. The items can be packed in a pair. You need to order the number of packing material required for each item. As a precaution, you decide to order an extra packing material for each item.
The syntax for an item C4 and its quantity in D4, the syntax is given as:
=(EVEN(D4))/2 + 1
EVEN(D4)/2 will give the number of packing material required for packing 6 Vase and it will return 3. Since we require one extra packing material, one is added to 3. It is to be noted here that the syntax is not given as =EVEN(D4/2).
Things to Remember About the EVEN
- The EVEN Excel returns the nearest even value of number after rounding up and is adjusted away from zero.
- The EVEN Excel returns equal or a higher value when given a positive number.
- The EVEN Excel returns equal or lower value when given a negative number.
- If number is a non-numeric value, EVEN returns the #VALUE! error value.
This has been a guide to EVEN Function in Excel. Here we discuss the EVEN Formula in excel and how to use EVEN in Excel along with EVEN function example, and downloadable excel templates. You may also look at these useful functions in excel –