Excel ODD FunctionWhen you hear the name of ODD for the first time, it may sound like some normal function that tells whether a number is odd or not. But that definition is not limited to this as you can integrate the Excel ODD function with other functions and solve various complicated problems. In this tutorial, we will discover the basic and advanced applications of the Excel ODD function using several examples such as its usage will become crystal clear. What is Excel ODD Function?The Excel ODD function rounds a positive number up and negative number down to the nearest ODD integer. For example if you pass the following input =ODD (2.5), this function will return the rounded output of 3, and if the specified input is =ODD(2.5) then the returned output will be 3. The Excel ODD function returns a numeric value by rounding the specified number to the next odd integer. ODD function always rounds numbers away from zero. Therefore, the positive numbers become larger and negative numbers become smaller (i.e., more negative). This function takes just one parameter (number), which should be a numeric value. If you pass nonnumeric in its argument, it will throw a #VALUE! error. SyntaxParametersNumber (required): This parameter represents the cell which you want to convert to the nearest ODD integer. ReturnThis function returns an ODD integer after rounding a positive number up and negative number down to the nearest ODD integer. Points to Remember about ODD Function
ExampleExample 1: Use ODD function to round off the number on the following examples. Follow the below steps to round off the numbers to its nearest ODD number: STEP 1: Insert a helper column Add a column next to "Numbers" and type the column name "ODD Round off" on top of the cell. It will look similar to the below image: In the helper column, we will type the ODD function for every row and find round off the numbers to its nearest ODD number. NOTE: As the above image shows, we have formatted the column with borders and font to make the worksheet more visually attractive.STEP 2: Insert the ODD formula The next step is to enter the formula so put your cursor in the second row of your helper column start typing: = ODD( It will look similar to the below image: STEP 3: Add the number parameter The first parameter includes "Number" which represents the cell which you want to convert to the nearest ODD integer. Here, cell reference B4 holds our numeric value. So our formula becomes: =ODD (B5) It will look similar to the below image: STEP 4: ODD will return the nearest ODD integer number ODD (B5) will return the ODD integer number after rounding it off to its nearest ODD number. STEP 5: Drag the formula to other rows to repeat Put your cursor on the formula cell and take it towards the right of the rectangular box. You will notice that the cursor will change into the '+' icon. It will look similar to the below image: Drag the + icons to all the cells below it. This will automatically copy the ODD function to all the cells. As a result, ODD will return the integer numbers after rounding it off to its nearest ODD number. STEP 6: Explanation of each Output
Example 2: In the below table, find all the odd numbers using the Excel ODD Function and count how many ODD numbers are there in the list.To find the count of odd numbers from the above list, we will use a combination of Excel ODD and COUNTIF function. Follow the below given steps to achieve the same: STEP 1: Insert two helper columns
It will look similar to the below image:
STEP 2: Insert the formula = cell reference The next step is to enter the formula. Start the formula with the = (sign), and then we will pass the reference of our data cell. In our case, our data is in cell B5, so our formula becomes: =B5 It will look similar to the below image: STEP 3: Insert the ODD formula
It will look similar to the below image:
It will look similar to the below image: The above formula will check whether the value in cell B5 is equal to the closest ODD number. If it is equal, it will return TRUE; if it is not equal, it will return FALSE. STEP 4: Formula Will return Boolean False The combination of above formula will return a Boolean value False since A2 (11.11) and ODD (11.11) are not equal. STEP 5: Drag the formula to other rows to repeat
You will have the following Boolean outputs: STEP 6: Apply COUNTIF formula to get the total count of even numbers We will use the Excel COUNTIF formula in cell E6 to get the total count of all TRUE values, in the list. So the formula will be as follows: Refer to the below image: STEP 7: COUNTIF will return the total number of even numbers Here COUNTIF formula will count all the TRUE values present in the range C5: C14 and return the sum of all the TRUE values. The total number of TRUE values in the range C5: C14 is 4. That means there are 4 odd numbers in the list. Example 3: In the below table we have some data inputs that are nonnumeric, let's see what happens when we use the Excel ODD Function.Follow the below given steps to achieve the same: STEP 1: Insert a helper column Add a column next to "Items" and type the column name "ODD Number" on top of the cell. It will look similar to the below image: In the helper column, we will check for each given number whether is it is EVEN or ODD. STEP 2: Insert the ODD formula
It will look similar to the below image: STEP 3: ODD will return an error The ODD function only works for numeric values. If you supply any nonnumeric value in its parameter, it will throw an #VALUE! error. In the B5 cell, we have supplied string values. Therefore the ODD (B5) formula has returned the #VALUE! error as shown in the below image: When you drag the formula for the rest of the cells, you will find the same #VALUE! error because all the values are nonnumeric.
Next TopicExcel SUMPRODUCT Function
