How to use the EVEN function

What is the EVEN function?

The EVEN function rounds a number up to the nearest even whole number.

1. Introduction

What is an even number?

An even number is a number that can be evenly divided into groups of two. An even number always ends with a digit of 0, 2, 4, 6, or 8.

How to round numbers?

Rounding is a method to simplify a number by reducing its digits while keeping its approximate value close to the original value.

There are a few common ways to round:

• Round to a set number of decimal places, rounding 2.13579 to 2 decimal places gives 2.14.
• Round up or down to the nearest integer, rounding up 2.3 gives 3. Rounding down 2.3 gives 2.
• Round to a set increment, rounding to the nearest 10 rounds 17 to 20.
• Round to significant figures, rounding 2.333 to 3 significant figures gives 2.33.

When rounding, look at the first digit after where you want to round. If it's 5 or more, round up. If less than 5, round down. Rounding makes numbers cleaner and easier to work with in many everyday situations, however, they may also cause rounding errors like rounded values can compound errors. Rounding measurements and constants may reduces precision. It is better to round numbers after performing calculations than before.

What is a decimal place?

A decimal place refers to each position held by a digit in a number. The first decimal place is the tenths place (1/10), the second is the hundreds place (1/100) and so on.

What is an integer?

An integer is a whole number that can be positive, negative, or zero, but not a fraction or decimal.

What is a "a set increment"?

A "set increment" is a fixed regular number interval that is used as the rounding value or unit.

• Rounding to the nearest 10 uses a set increment of 10. Valid rounded values are ..., -20, -10, 0, 10, 20, 30, ...
• Rounding to the nearest 0.5 uses a set increment of 0.5. Valid values are ..., 2.0, 2.5, 3.0, 3.5, ...

What other Excel functions round numbers?

ROUND	Rounds a number to a specified number of digits
ROUNDUP	Rounds a number up, away from zero
ROUNDDOWN	Rounds a number down, towards zero
MROUND	Rounds a number to the nearest multiple of a specified value
CEILING	Rounds a number up to its nearest multiple.
ODD	Returns number rounded up to the nearest odd integer.
EVEN	Rounds a number up to the nearest even whole number.
FIXED	Rounds a number to the specified number of decimals, lets you ignore comma separators.

Formula in cell C3:

2. Syntax

EVEN(number)

number

The number you want to round to the nearest even integer.

3. Example 1

This example demonstrates different numerical values in column B and how the EVEN function handles these values in column C.

Formula in cell C3:

• The first value is 0.06, the EVEN function rounds the number up to its nearest even whole number which is 2.
• The second value is 4.28, the EVEN function rounds the number up to its nearest even whole number which is 6.
• The third value is 8.56, the EVEN function rounds the number up to its nearest even whole number which is 10.
• The fourth value is -3.74, the EVEN function rounds the number down to its nearest even whole number which is -4.
• The fifth value is A, the EVEN function can't evaluate text strings. The output is a #VALUE! error.

4. Example 2

Create a conditional formatting formula that highlights even rows in cell range B2:D24?

The following formula highlights rows based on even row numbers in a given color.

Conditional formatting (CF) formula:

Here is how to set it up:

1. Select the cell range you want to apply conditional formatting to. In this example, cell range B2:D24
2. Go to tab "Home" on the ribbon.
3. Press with left mouse button on the "Conditional Formatting" button on the ribbon, a popup menu appears.
4. Press with left mouse button on "New Rule.."
5. Type the CF formula in "Format values where this formula is true:" field.
   
6. Press with left mouse button on the "Format..." button above the "Cancel" button. A new dialog box appears.
7. Go to tab "Fill"
   
8. Pick a color.
9. Press with left mouse button on "OK" button to dismiss the dialog box.
10. You are now back to the previous dialog box.  Press with left mouse button on "OK" button to dismiss this dialog box as well.

4.1 Explaining conditional formatting formula

Step 1 - Calculate the row number based on a cell reference

The ROW function calculates the row number of a cell reference.

Function syntax: ROW(reference)

ROW(A2) returns 2.

Step 2 - Round the row number up to the nearest even whole number

The EVEN function rounds a number up to the nearest even whole number.

Function syntax: EVEN(number)

EVEN(ROW(A2))

becomes EVEN(2) and returns 2.

Step 3 - Compare row number with rounded value

ROW(A2)=EVEN(ROW(A2))

becomes

2=2 and returns TRUE. Row 2 (B2:D2) in cell range B2:D24 is highlighted.

5. Example 3

Cell range B3:D24 contains measurements from a statistical study made by a researcher, extract even numbers from these measurements?

The following formula works only in Excel 365, the TOCOL function exists only in Excel 365 as far as I know.

Excel 365 formula in cell F3:

The Excel 365 formula returns an array that spills to cells below as far as a needed and automatically.

Excel formula for earlier Excel versions:

The formula for earlier Excel versions must be copied manually to cells below as far as needed.

Both formulas return even numbers from cell range B3:D24, odd numbers are left out.

Explaining Excel 365 formula

Step 1 - Round values up to nearest even whole number

EVEN(B3:D24)

Step 2 - Compare values to rounded values

The equal sign lets you compare value to value, both arrays are equal in size in this example. The evaluation is not case sensitive, however, our values are only numerical values so this fact can be ignored in this example.

B3:D24=EVEN(B3:D24)

The result of this evaluation is TRUE or FALSE, these are boolean values. They allow us to filter values in the next step.

Step 3 - Filter even values

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(B3:D24=EVEN(B3:D24),B3:D24,"")

The logical_test argument is great for our array containing boolean values TRUE or FALSE. The IF function allows us to extract even number and filters the remaining values out based on these boolean arrays. The values that correspond to FALSE are replaced with "" which represents nothing or empty.

Step 4 - Rearrange values to a single column

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

TOCOL(IF(B3:D24=EVEN(B3:D24),B3:D24,""))

The TOCOL function rearranges the multi-colomn array to a single column. This makes the data easier to manage, manipulate and sort.

Step 5 - Sort values

The SORT function sorts values from a cell range or array

Function syntax: SORT(array,[sort_index],[sort_order],[by_col])

SORT(TOCOL(IF(B3:D24=EVEN(B3:D24),B3:D24,"")))

The SORT function sorts the numerical values from small to large.

'EVEN' function examples

The following article has a formula that contains the EVEN function.

Functions in 'Math and trigonometry' category

The EVEN function function is one of 63 functions in the 'Math and trigonometry' category.

Excel function categories

Excel categories

Leave a Comment