ODD Function
Rounds up any number to its nearest odd value.
What is the ODD Function?
The ODD function in Excel rounds up any number to its nearest odd value. When the number is positive, it is rounded to a more significant positive weird number, whereas when the number is negative, it is rounded to a smaller negative odd number.
Excel primarily offers functions such as ROUND, ROUNDDOWN, and ROUNDUP, which help to round numbers, as well as TRUNC and INT, which help to truncate the digits after the decimal.
Having a dedicated function that rounds a number to the nearest odd number shouldn’t be surprising since it can be quite a valuable tool to change the numbers into their odd counterparts.
You rarely change the numbers if you are working on critical financial data. However, we never know when we might need it, right?
This article will guide you on the syntax for the ODD function, how to use the part and a couple of examples.
Key Takeaways
- The ODD function returns the following odd number for positive and negative numbers.
- If you input a non-numeric value as the argument, the function will return the #VALUE! Error.
- An odd integer will return the same number as a result, whereas an even integer will return the next odd number.
- All the positive numbers are rounded to the same or the following larger number, whereas the negative numbers are rounded to the same or the smaller next odd number after using the function.
- The function accepts a single argument to return the criteria-based number.
Understanding the ODD function
The ODD is categorized as a Math and Trigonometric function that will round a negative or positive number to its nearest odd number.
For example, if you have four numbers, 13.987, 13.98, 13.9, and 13, the function will return the result as 15, respectively.
Although 13 is also an odd number, the function returns the following positive odd number, which in this case is 15.
The decimal digits for the rest of the numbers are ‘truncated’ to return the integer value.
We know the general rounding principle for the numbers, i.e., if the succeeding digit to the right lies between 5 to 9, then the number gets rounded up; if the next digit to the right lies between 0 to 4, then the number gets rounded down.
However, the rounding principle does not apply in the case of the ODD function, where the number is ‘always’ rounded away from zero.
The function was introduced in the Excel 2000 version and has been present in all subsequent versions.
ODD Function Formula
The syntax for the function is
=ODD(number)
where,
number - (required) the number which will be rounded up to the next odd integer.
ODD Function Example
We got your back, folks. We won’t bore you with the ‘how to use section for a simple single argument function.
However, it’s pretty clear what you need to do if you return the following odd number for a numerical value.
Suppose you have the Excel data, as illustrated below:
Here, we have positive and negative numbers with varying decimal digits and date and time values.
To return the next odd number, we will use the formula =ODD(B3) in a cell in cell C3 and drag it down to cell C10 to get the result:
Interpretation
We get some quite surprising results in column C.
- First, if we have an odd integer and use the function, the result returned is the same. For example, the numbers 17 and -21 produce the same results.
- If the numbers are decimal, for example, 17.489, 14.211, 14.2, then no matter whether the truncated numbers are odd or even, the result would always be the following odd number.
- Finally, we have the date and time values, which we know are serial and decimal, respectively.
- For any time except 12:00 AM, the result would always be 1 since the time lies in decimal numbers between 0 and 1, and the following odd number is 1.
- As for the date values, the serial number corresponding to 8th November 2022 is 44873. Since the integer value is an odd number, we get the same result for the function.
- However, the scenario would have been entirely different if the date had an even serial number.
ODD function and negative numbers
In this previous example, we saw what effect the function had on the negative number. However, we feel that’s insufficient to grasp what happens behind the scenes.
We saw what effect the function had on positive numbers. When a cell with a positive number was referenced, we either received the same result or the following odd number more prominent than the referenced number.
However, on the other hand, when you reference a negative number, then, the rounded values are either the same or smaller than the referenced number.
Looking at an example could help us to understand it better. For example, suppose you have some numbers in Excel, as illustrated below:
By using the formula =ODD(B3) in cell C3 and dragging it down to cell C6, we get the result:
As per our understanding of the positive numbers, when we had a number like 17.489, the expected number would be 19. When the sign is flipped, the result is the same.
The only change is that the new number returned is smaller than the original number.
However, the function’s principle remains the same: we are rounding away from zero to return the following odd number.
ODD vs. EVEN function
Did we speak about the EVEN function before? Well, if we didn’t, it’s finally time to shed some light on this function that works exactly the opposite of its counterpart.
The EVEN is categorized as a Math & Trigonometric function that rounds up any number to its following number.
The EVEN function works on a similar principle where the positive numbers will be rounded to the same or larger even numbers. In contrast, the negative numbers will be rounded to the same or smaller even numbers.
The syntax for the function is
=EVEN(number)
where,
number - (required) the number which will be rounded to the next even number.
Assume you have the data as illustrated below:
Let’s see and compare what results we get for each function. As usual, we will use the formula =ODD(B3) in cell C3 and drag it down till cell C10, which gives the result:
Similarly, the formula =EVEN(B3) will give us the next even numbers in range D3:D10 as below:
Behind the scenes, the digits after the decimal get truncated to get an integer value which is then evaluated to either return the same or the next even number.
We get the same number as our result when we have an even positive or negative number.
ODD vs. ROUND function
If the priority is to round the numbers given in the data, the function that should come first to your mind should always be ROUND.
The ROUND is categorized as a Math & Trigonometric function that rounds a number to a specified number of decimal places.
The function works on the general rounding principle, i.e., if the succeeding number to the right lies between 5 and 9, it gets rounded up, whereas if the number lies between 0 and 4, it gets rounded down.
The syntax for the function is
=ROUND(number, num_digits)
where
- number - (required) the number which will be rounded to specified decimal places
- num_digits - (required) the number of decimal places that you want to round the value to
Let’s see an example of how the results differ for both functions. Suppose you have the data as illustrated below:
We will use the formula =ODD(B3) in cell C3 and drag it down to cell C7, which gives the result:
On the other hand, we will use the formula =ROUND(B3,0) in range D3:D7 and limit the digits after the decimal to zero to give the result:
For the number 17.582, the next number to the right of the zeroth position is 5. Since 5 lies between 5 and 9, the number gets rounded to 18.
Similarly, 13.25 has two as the succeeding number to the right of the zeroth position that lies between 0 and 4. As a result, the number gets rounded down to 13.
It is quite evident from the results why Accountants and Financial Analysts prefer to use the ROUND function for accurate numerical calculations instead of the EVEN or ODD function.
Things to remember about the ODD Function
We know that the ODD function was introduced in Excel 2000 version and has been present in all the subsequent versions.
But what if the function never existed? Boom! Just vanished.
Are you still able to perform a similar operation based on the functions present in Excel?
The answer is ‘Yes.’ If you aren’t aware, there exists another function called ISODD that evaluates whether a number is odd.
If the number is odd, the function returns the result as TRUE or else FALSE.
Combine it with the versatile IFS statements, and get the formula that works precisely like the ODD function.
For example, Suppose you have the data as illustrated below:
We will use the
formula =IFS(AND(INT(B3)=B3,ISODD(B3)),INT(B3),ISODD(INT(B3)),INT(B3)+2,ISEVEN(INT(B3)),INT(B3)+1,AND(INT(B3),ISEVEN(B3)),INT(B3)+1)
in cell D3 and drag it down till cell D7, which gives the result:
Phew! That might have taken a bit of time to digest. Please don’t judge us for the formula! As you know, there can be innumerable ways to get the desired result, and this is just one of them.
As you can see how complicated the formula is, we are blessed that Microsoft has bestowed us with the ODD function since the beginning.
or Want to Sign up with your social account?