Round Numbers to the Nearest Even Integer in Excel

Round Numbers to the Next Even Integer in Excel with the EVEN Function

 

Excel's EVEN function is used to round decimal values to an even integer while removing the decimal portion of the number. All values, whether even or odd, are rounded to even integers by the function.

You might do this when dealing with items that come in pairs that need to go in boxes for shipment. The box is full once the number of objects, rounded to the nearest two, matches the capacity of a box. 

Note: these instructions apply to Excel 2019, 2016, 2013, 2010, Excel 2019 for Mac, Excel 2016 for Mac, Excel for Mac 2011 and Excel for Office 365. 

Rounding Positive vs. Negative Numbers

The EVEN 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.

Positive numbers get 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 alter only the appearance of data by changing the number of decimal places displayed; the EVEN function 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 is the value to be rounded. This argument can contain the specific data, 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.

You can input the function by typing the function name and argument into the desired cell or by 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
  2. Click on the Formulas tab of the ribbon.
  3. Choose Math & Trig from the ribbon to open the function drop-down menu.
  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.
  10. Click on another cell to see the results of the EVEN function in cell C2.

 

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 you entered the argument for the EVEN function as a relative cell reference.

The results of this operation should be:

  • Cells C3 and C5 should display 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 appears 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. You can check for errors and convert text to numbers in a variety of ways.