Round Numbers to the Nearest Even Integer in Excel

of 01

Excel EVEN Function

Round Numbers to the Next Even Integer in Excel with the EVEN Function
Round Numbers to the Next Even Integer. © Ted French

Excel's EVEN function can be used to round decimal values to an even integer while removing the decimal portion of the number.

As shown in column C in the image above, all values - whether even and odd - are rounded to even integers by the function.

Rounding Positive vs. Negative Numbers

The function does not follow the rules for rounding that many of Excel's other rounding functions follow. Instead, it always rounds numbers away from zero regardless of whether a number is negative or positive.

Rounding away from zero results in positive numbers being rounded up to the next highest even integer regardless of the value of the rounding digit while negative numbers are rounded down to the next lowest even negative integer.

In the image above, the values 6.023 and 7.023 are rounded up to 8 while the values -6.023 and -7.023 are rounded down to -8.

Rounding Data and Calculations

Unlike formatting options that only alter the appearance of data by changing the number of decimal places displayed; the EVEN function actually alters the data in a worksheet.

The EVEN function can, therefore, affect the results of other calculations in a worksheet that make use of the rounded data.

The EVEN Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the EVEN function is:

= EVEN ( Number)

Number - (required) the value to be rounded. This argument can contain the actual data for rounding or it can be a cell reference to the location of the data in the worksheet.

EVEN Function Examples

The example in the image above uses the EVEN function to round several decimal values to the next even integer.

The function can be entered by typing the function name and argument into the desired cell or it can be entered using the function's dialog box as outlined below.

The steps used to enter the function into cell C2 are:

  1. Click on cell C2 to make it the active cell - this is where the results of the EVEN function example will be displayed
  2. Click on the Formulas tab of the ribbon menu
  3. Choose Math & Trig from the ribbon to open the function drop down list
  4. Click on EVEN in the list to bring up the function's dialog box
  5. In the dialog box, click on the Number line
  6. Click on cell A2 in the worksheet to enter that cell reference into the dialog box
  7. Click OK to complete the function and close the dialog box
  8. The answer 8 should appear in cell C2 since this is the next highest even integer after 6.023
  9. When you click on cell C2 the complete function = EVEN ( A2 ) appears in the formula bar above the worksheet

Copying the Formula With the Fill Handle

Rather than recreate the formula for the other three examples, the first formula can be quickly copied using the fill handle or copy and paste since the argument for the EVEN function was entered as a relative cell reference.

The results of this operation should be:

  • Cells C3 and C5 should contain the value -8 since this is the next lowest even integer after the values -6.023 and -7.023;
  • Cell C4 should contain the value of 8, which is the next highest even integer after 7.023.

#VALUE! Error Value

The #VALUE! error value is returned if Excel does not recognize the data entered for the Number argument as a number. One possible explanation would be numbers entered as text data.