What is ODD Function in Google Sheets?
The ODD function in Google Sheets rounds up a number to the nearest odd integer. If the number is already odd, it will not change, but if it is even, it will be rounded up to the next odd number. It means that it will always round a number away from zero to the next highest odd number, be it positive or negative.
The function contains many uses. It checks whether the provided value is odd. Such situations may arise when you make seating arrangements with an odd number of seats or need to find an odd number of quantities for packaging. In the example below, we have a couple of numbers in Column A and apply the function ODD() to them along with the cell reference as an argument in Column B. We can see from the below results which show how the ODD function works on them.
Key Takeaways
- The ODD function rounds a supplied number to the nearest odd integer.
- The ODD formula in Google Sheets is as follows:
- =ODD(number)
- Here, the number is a mandatory argument, and we want to round the value to the nearest odd number.
- The ODD function rounds numbers away from zero. A positive number increases, while a negative number becomes more negative.
- The ODD function in Google Sheets does not affect negative numbers.
- For specific requirements, the ODD function can be used along with other functions like SUMPRODUCT, IF, and SUM.
Syntax
The syntax of the ODD function in Google Sheets is very simple.
=ODD(number)
- number: It is the value or reference to the cell containing the value you want to round to the nearest odd integer.
For instance, if you want to round if 1,000 to the nearest odd number, you can use the ODD as =ODD(1000). The result would be 1001.
How to Use ODD Function in Google Sheets?
We can find the nearest odd number of an integer using ODD in the following ways.
- Manually entering the ODD function
- Through the Google Menu bar
Manually entering the ODD function
Let us look at a basic example of using the ODD function to determine if a number is odd or to extract odd numbers from a list. Below is a list of numbers.
Step 1: The numbers are below in Column A. Type the equal sign (=) in the spreadsheet and then type ODD. After opening the parenthesis (, enter the cell reference or value you want to check. Close the parenthesis and press Enter.
The formula we enter is =ODD(A1) in cell B1. Press Enter.
Step 2: You get the result. To find the result for the other numbers, drag the formula up to B3 and check the results.
As seen, it is easy to enter the ODD function manually along with its argument, which can either be a cell reference containing the value or the value itself.
Through the Google Menu bar
To use the ODD function,
Go to the following path in the Google menu bar.
Insert -> Function -> Math -> ODD.
This function is useful if you’re working with data that requires rounding to odd integers.
Examples
To understand how ODD functions, let’s examine some interesting examples. The function’s real value appears when you combine it with other functions to perform more complex calculations.
Example #1
Many products have a pricing strategy for rounding to the nearest odd dollar. Let us look at an example of rounding some dollar pricing to the nearest odd dollar as a pricing strategy.
Step 1: Here is the list of prices entered in a spreadsheet.
Step 1: First, let us use the ODD function in cell B1. Type the following function
=ODD(A1) and press Enter.
You get 25 as it is the nearest odd number away from zero for 24.99.
Step 2: After applying the formula to the other cells, you can observe the output. To do so, drag the formula from cell B1 to B4.
Step 3: The outputs are the nearest odd numbers to the ones in Column A. Even though 11 is an odd number by itself, 11.99 is treated as greater than 11, and hence, you get 13, which is the next odd number.
Example #2 – Using ODD with IF
Let us look at an example. We have a list of numbers in column A. Now, you wish to track all the evn numbers and round them to the nearest odd integer. If the number is not even, we wish the result to remain unchanged.
Step 1: Now, we are going to check each number for even numbers and if so, we round them to the nearest odd number. Enter the formula given below in cell B1. Press Enter.
=IF(ISEVEN(A1), ODD(A1) , A1)
Here, the first number is odd. So, the output is not changed.
Step 2: Now, drag the formula up to cell B10 to get the corresponding results in Column B for each number in Column A.
Step 3: Now, we see that all the even numbers have been converted to the corresponding odd number, while the odd numbers remain the same.
The ODD-IF combination is very useful for condition-based calculations.
Example #3 – Using ODD with SUMPRODUCT
You can use the ODD function in Google Sheets in business. We often encounter scenarios of finding the total cost by multiplying the number of items with their unit cost where we can conveniently use SUMPRODUCT. We can also pair ODD with SUMPRODUCT. In this example, we have some prices. We wish to round each one up to the nearest odd number and then calculate the total cost.
Here is how you can do it:
Step 1: Let us enter the cost of some items in Column A.
Step 2: Let us enter the formula to get the final sum in Column B. Those cells with an even number are rounded up to an odd number. Here, it should be cells A2, A3, A4, and A7. Now, let us use the ODD function in Google Sheets as follows:
=SUMPRODUCT(ODD(A1:A8))
This formula uses the ODD function to check each number in the range A1 to A8 and convert them to an odd number if the number is even. It then sums the results. A simple way to combine rounding and summing of numbers simultaneously!
Important Things to Note
- The ODD function takes only a single argument, which should be a numeric value or a cell reference.
- For non-numeric values, when you use the ODD function, you get the #VALUE! Error.
- A blank cell gives an output of one for the ODD function.
- Similar to the ODD function, we have the EVEN in Google Sheets to round up to the even numbers.
- The ODD function can be used so that fractional values are rounded to odd numbers.
- To create complex conditional logic, combine ODD with the IF function in Google Sheets.
- If a decimal is close to an even number, the ODD function will round up to the next odd number. For instance, =ODD(2.7) returns 3 while =ODD(5.1) returns 7.
Frequently Asked Questions (FAQs)
The ODD function works not only with positive numbers, it works with negative numbers too. It is used to round a negative number towards the next more negative odd integer. For example:
=ODD(-1.5) will return -3.
=ODD(-4.2) will return -5.
Here are some of the uses of the ODD Function:
1. Inventory Management: Sometimes, products are required to be packaged in odd quantities. You can use the ODD function to ensure that inventory quantities are always odd.
To generate odd Number sequences that can be used for ID cards, to assign odd-numbered seats in a theatre, etc.
2. Adjusting Prices to Odd Values: Many retailers use odd-numbered prices, such as $9, $29, etc., to make a product seem more discounted.
If the cell contains a non-numeric value, you get the #VALUE error.
The rounding up to odd numbers might not result as you expect due to the limits in floating-point arithmetic when dealing with a huge number.
Download Template
This article must be helpful to understand the ODD Function in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is ODD Function in Google Sheets. We learn its syntax & how to use it to round up a number to the nearest odd integer. You can learn more from the following articles –
Leave a Reply