How to Round Floating Values to Even or Odd Numbers

We discussed rounding of floating values using ROUND, ROUNDUP and ROUNDDOWN functions. What if I want these rounding of numbers to be even or odd?

Today, we will learn how to round values using EVEN, ODD, CEILING and FLOOR Functions.

Let’s understand each of these functions with some examples.

EVEN

Using EVEN function, a number can be rounded up to the nearest even integer or rounded down to the nearest even negative integer.

Syntax:

=EVEN(number or cell address)

For example, 13.6 with EVEN function will give the result 14, 16.2 will give the result as 18 and -3 will give -4 as shown in the image below:

We can even use number itself as an argument within the function. Like =EVEN(13.6) and the results will be the same.

ODD

Just as the name suggests ODD function rounds a number up to the nearest odd integer or down to the nearest odd negative integer.

Syntax:

=ODD(number or cell address)

For example, 13.6 with ODD function will give the result 15, 16.2 will give the result as 17 and -4 will give -5 as shown below:

We can even use the number as an argument within the function. Like =EVEN(13.6) and the results will be the same.

CEILING

The CEILING function rounds a number up to the nearest specified multiple.

This function is useful for rounding wages and salaries related data. As 1 or 2 cents or paisa have been discontinued we can round it to the nearest 5 cents or rupees.

Syntax:

=CEILING(number or cell address, multiple)

For example, wages for a worker is 2365.81. Applying the CEILING function here will give the result 2365.85. The formula applied here is =CEILING(2365.81,.05), where 0.5 is the given arguments. This argument can be put as per our requirement.

CEILING function with different conditions have been applied and shown in the image below:

Please note that for negative numbers your argument for rounding should also be negative otherwise there will be a #NUM error.

FLOOR

FLOOR function is used to round down a number to the nearest specified multiple. It is somewhat similar to CEILING.

Syntax:

=FLOOR (number,multiple)

For example, wages for a worker is 2365.81. Applying the FLOOR function here will give the result 2365.8. The formula applied here is =FLOOR(2365.81,.05), where 0.5 is the given arguments. This argument can be put as per our requirement.

FLOOR function with different conditions have been applied and shown in the image below:

Just like CEILING function while using FLOOR function, for any negative number the argument should be negative otherwise the result will be a #NUM error.

We can round numbers to decimal places even by using the increase or decrease on the Formatting toolbar. We can do it using cell format function and also using custom format wizard.

Using all the above option doesn’t change the value of the number but only changes the display of the numbers

For 123.654 decreased to 123.65 using decrease function from format toolbar, it will display 123.65 in the worksheet but 123.654 in the address bar.

If you have queries please share them in comments. I will be more than happy to help you.