Excel ODD Function
ODD Function is an inbuilt function in excel as the name suggests it rounds any given number to its odd value, the number can be both 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) then the result is 3.
The ODD formula has only one compulsory parameter i.e. number.
- number: A numeric value that will be rounded up to the nearest odd integer.
How to use the ODD Function in Excel?
In this example, let’s consider a set of positive integers 40, 18, 10, 26, 19, 20, 14, 17, 30 and 27 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, 19, 21, 15, 17, 31 and 27.
In this example, we take the sample of negative integers and then apply the ODD formula on them.
Let’s consider a set of positive integers -40, -18, -10, -26, -19, -20, -14, -17, -30 and -27 to understand the working on ODD. Now apply the ODD Formula in 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.
In this example let’s take positive point values for the ODD.
Here point value first-round off to near integer value than odd works. For value 27.9 the odd return the 29 as output because 27.9 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.
In this ODD function, example let’s take negative point values for the ODD.
Let’s 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 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.
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
- If the supplied number is non-numeric value then the ODD will return the #VALUE! Error.
- 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.
This has been a guide to Excel ODD Function. Here we discuss the ODD Function and how to use it along with excel example and downloadable templates. You may also look at these useful functions in excel –