ODD Function in Excel

Publication Date :

Blog Author :

Table Of Contents

arrow

Excel ODD Function

The ODD function is a built-in function in Excel. As the name suggests, it rounds any given number to its odd value. The number can be negative or positive. So, for example, if the given input is =ODD(-1.5), the rounded result will be -3, and if the given input is =ODD(1.5), the result is 3.

Syntax

ODD Formula

The ODD formula has only one critical parameter: number.

Compulsory Parameter:

  • number: A numeric value that will be rounded to the nearest odd integer.

How to use the ODD Function in Excel?

Example #1

In this example, let us consider a set of positive integers 40, 18, 10, 26, 19, 20, 14, 17, 30, and 27 to understand the working of the ODD function. Now, apply the ODD formula in the cell, as shown in the below table. This function will round up all values to the nearest odd numbers like 41, 19, 11, 27, 19, 21, 15, 17, 31, and 27.

Example #2

In this example, we take the sample of negative integers and then apply the ODD formula to them.

Let us consider a set of positive integers -40, -18, -10, -26, -19, -20, -14, -17, -30 and -27 to understand the working of the ODD function. Now, apply the ODD formula in the cell as shown in the below table. This function will round up all values to the nearest odd numbers as -41, -19, -11, -27, -19, -21, -15, -17, -31 and -27.

Example #3

In this example, let us take positive point values for the ODD.

Here, point value first-round off to near integer value than odd works. For example, for value 27.9, the odd returns the 29 as output because 27.9 was first considered as >27 ~ 28, then odd rounded up to the nearest integer value.

For the below table values 40.18, 18.1, 10.26, 26.19, 19.19, 19.14, 14.17, 17.3, 30.27 and 27.9 the output will be 41, 19, 11, 27, 19, 21, 15, 17, 31 and 29.

Example 3

Example #4

In this ODD function, for example, let us take negative point values for the ODD.

Let us consider a set of negative point values -40.18, -18.1, -10.26, -26.19, -19.19, -19.14, -14.17, -17.3, -30.27 and -27.9 to understand the working on ODD. Now, apply the ODD formula in the cell as shown in the below table. This function will round up all values to the nearest odd numbers as -41, -19, -11, -27, -21, -21, -15, -19, -31 and -29.

Example 4

The ODD can be used as a VBA function.

Dim ODDnumber As Long

ODDnumber = Application.worksheetfunction.odd(“14”)

Msgbox(ODDnumber) // Return the nearest odd value in message box.

The output will be “15” and printed in the message box.

Things to Remember

  • The ODD Excel function may return the # VALUE! Error if the supplied number is a non-numeric value.
  • Irrespective of the value (+ve and –ve) of the number, a value is rounded up and adjusted away from zero.
  • If the number is an odd integer, no rounding occurs.