EVEN Function
Rounds up any number to its nearest even value
What is the EVEN Function?
The EVEN function in Excel rounds up any number to its nearest even value. So, to be precise, if we have a positive number, the function rounds up the number, while if we have a negative number, the function will round down the value.
What’s the reason behind it?
As a financial analyst, directly changing the numbers in Excel hardly occurs, but wherever applicable, you can use the function to round and return even numbers.
In this article, you will learn the syntax and uses of the function in your profession.
Key Takeaways
- The function rounds up a negative or positive number to its nearest next even number.
- The function takes only one argument as a numerical value and returns the next nearest criteria-based number.
- If you input a non-numeric value as the argument for the function, Excel will return the #VALUE! Error.
- A number divisible by two will give the same result, while an odd integer will round up to the next even integer.
- All the positive numbers are rounded up to become more significant in value. In contrast, all the negative numbers become smaller since they both move away from zero after using the function.
Understanding the EVEN function
The function is categorized as a Math and Trigonometric function that rounds up a negative or a positive number to the next even number.
For example, if you have the number 14.832 in Excel, the function returns the result as 16.
We understand how rounding up generally works - if the next digit to the right is between five and nine, the number rounds up.
However, the function rounds up the value to the nearest even value. Therefore, it does not matter even if the next digit to the right is between zero to four. So, for example, a number such as 14.2 will also give the following number as 16.
Finally, if you have integers in Excel, you will get the following numbers only when you input odd numbers, not even numbers. For example, 17 will give you 18, but 18 won't give you 20. However, it will still give you 18 as a result in Excel.
Microsoft introduced the function in the Excel 2000 version, which has been present in all of the latest versions ever since.
EVEN function Formula
The syntax for the function is:
=EVEN(number)
where,
number = (required) the number which will be rounded up to the next even integer.
EVEN Function Example
Since the function takes only one argument, it is easy to use and understand. Assume that you have data in Excel, as illustrated below:
Our dataset has decimal numbers, integers, dates, and time values. Therefore, by using the formula =EVEN(B3) in cell C3 and dragging it down to cell C9, we will get the result:
Interpretation
Let's interpret the above example:
- In cell C3, the number 17.489 rounds up to the next even integer, 18. Note that although our subsequent digit to the right of the decimal is between zero and four, the value still rounds up to the next number divisible by two.
- A similar scenario is in cell C4, where 14.211 rounds up to 16, its next even number/integer.
- The number of decimals doesn't matter. We get an integer value when we use the function, as shown in cell C5, with a result of 16.
- As we had previously seen, when you use an even integer inside the function, the function returns the same number as in cell C6.
- If you have an odd integer, the function returns the next even integer, i.e., 17 becomes 18 in our example.
- You might be aware that time can also be represented in decimal values, such that 0 equals 12:00 AM whereas 11:59 AM equals 0.99997, i.e., 24-hour clock times lie between 0-1. If the time is 10:36 PM or 0.9417, Excel returns the successive number as 2, which equals 48 hours or 12:00 AM.
- A date is in the form of a serial number in Excel. Since Excel uses the 1900 date system, the first date you will find in Excel is 1st January 1900. It holds the value 1.
As the date 05/22/2022 has a serial number 44703 (an odd number), the serial number rounds up to the next even integer, i.e., 44704. Therefore, if we reformat the value to 'short date', we will get the result as 05/23/2022.
However, even serial numbers for a date will return the same date.
EVEN function and negative numbers
We saw the effect of function on the positive numbers. To summarize, when you reference cells that have positive numbers, then the following number is either the same or more significant than the input value, i.e., all the rounded-up numbers will be the same or larger in value.
The question arises: What effect would the function have on negative numbers? Will those numbers as well round up to larger values?
Assume that you have the data in Excel as illustrated below:
When we use the formula =EVEN(B3) in cell C3 and drag it down up to the cell C6, we will get the result:
When you have negative numbers, the function rounds up to the next even number away from zero, i.e., all the rounded-up numbers will either be the same or smaller.
The function returns the same number if we have a negative integer, such as -14.
However, on the other hand, an odd negative integer, such as -17, will return the round-up value as -18, which is smaller than our input value and away from zero.
EVEN vs. ODD function
The function rounds up the value to the next even number, while the ODD function does precisely the opposite of its counterpart - it rounds up the value to the following odd number.
The ODD function has a similar syntax as:
=ODD(number)
where,
number = (required) the number which will be rounded up to the next odd integer.
Assume that you have the data as illustrated below:
We will see the effect of both functions on these numbers in Excel. By using the formula =EVEN(B3) and =ODD(B3) in columns C and D, respectively, we will get the result:
As you can see, all the numbers are rounded up to their following criteria numbers using both functions.
The ODD function returns the same number if you have an odd integer. For example, if the subsequent digit to the right of the decimal is between zero and four, the number rounds up to the next odd number.
Similarly, if you have negative numbers in Excel, both the functions return values equal to or smaller than input numbers and away from zero.
EVEN vs. ROUND function
The financial industry prefers using the ROUND function rather than the EVEN or ODD function to round the values. The reason is simple - precision and accuracy matter when much money is at stake.
The ROUND function rounds up a number to a specified number of decimal places. That means you can set up your result to have an 'n' number of decimal places while, on the other hand, the EVEN function returns an integer as a result.
The syntax for the ROUND function is:
=ROUND(number, num_digits)
where,
- number = (required) the number which will be rounded to specified decimal places
- num_digits = (required) the number of decimal places that you want to round the number to
Assume that you have some numbers in the spreadsheet as below:
We will round the value to zero decimal places using the ROUND function. Using the formulae =EVEN(B3) and =ROUND(B3,0) in columns C and D, respectively, we get the result as illustrated below:
Yes, the ROUND function does give rounded numbers, but there is one significant difference if the next number to the right lies between zero to four, the number rounds down. For example, 14.211, a number with the digit 2 after the decimal rounds down to 14.
On the other hand, if the following number to the right of the decimal lies between five to nine, then the number rounds up. For example, 13.91 has the digit 9 after the decimal, so the number rounds up to 14.
If you want to read more about the ROUND function, check out the dedicated article that we have covered in depth!
Things to remember about the EVEN Function
As a financial analyst or an accountant, you will work on many Excel sheets with decimal numbers.
The chances that you will use the function for those decimal numbers are rare. Why? Because wherever there is money involved, precision and accuracy matter.
Imagine the wrath accountants would face if they used the function to reconcile the accounts. Firstly, the books won't match, and secondly, they will probably face legal action for unethical practices.
In reality, no one is that dim-witted, but that's just a hypothetical scenario for why most financial analysts prefer using the ROUND function to get rounded values over EVEN, ODD, or any other equivalent function.
If there is a requirement for returning numbers divisible by two from the numerical values in the dataset, you can go ahead and directly use the EVEN function, which would do the work for you.
or Want to Sign up with your social account?