How to the Use ODD Function in Google Sheets

Published on

The ODD function is a math function in Google Sheets that rounds numbers up to the nearest odd integer. It handles positive, negative, and zero values in unique ways. Let’s explore its syntax and usage in detail.

Syntax of the ODD Function in Google Sheets

ODD(value)
  • value: This argument can be a negative number, a positive number, or zero.
  • When used with ARRAYFORMULA, the value argument becomes values, allowing you to process an array or range of numbers.

Still unclear? Don’t worry! The following examples will help you understand the ODD function in action.

Examples of Using the ODD Function

1. With Negative Numbers

  • Formula:
    =ODD(-4)
    Result: -5
  • Formula:
    =ODD(-5)
    Result: -5
  • Formula:
    =ODD(-100.25)
    Result: -101
  • Formula:
    =ODD(-101.25)
    Result: -103

2. With Positive Numbers

  • Formula:
    =ODD(126)
    Result: 127

3. With Zero

Formula:
=ODD(0)
Result: 1

Since this formula returns 1 for 0, if you refer to an empty cell, the output will also be 1.

Using the ODD Function with Arrays in Google Sheets

To round up a range of numbers to the nearest odd integers, you can use the ARRAYFORMULA with the ODD function. Here’s an example:

  • Formula:
    =ARRAYFORMULA(ODD(A1:A10))

This formula processes all values in the range A1:A10, rounding each up to the nearest odd integer.

Using the ODD function with ARRAYFORMULA to round numbers to the nearest odd integers in Google Sheets

Additional Tips

1. Generating Odd Numbers from 1 to 100

You can generate all odd numbers between 1 and 100 with the following formulas:

  • Using ODD, UNIQUE, and ROW:
    =ARRAYFORMULA(UNIQUE(ODD(ROW(A1:A99))))
  • A simpler alternative using the SEQUENCE function:
    =SEQUENCE(50, 1, 1, 2)

Both formulas produce the same result, but the SEQUENCE function is more efficient and easier to understand.

2. Filtering Rows Containing Odd Numbers

To filter a table by rows where a specific column (column A) contains odd numbers, you can use:

  • Formula:
    =FILTER(A1:B, A1:A=ODD(A1:A))

Alternatively, if you want to filter every alternate row in a dataset:

  • Formula using ODD and SEQUENCE:
    =FILTER(A1:B, SEQUENCE(ROWS(A1:B))=ODD(SEQUENCE(ROWS(A1:B))))
  • Simplified formula using ISODD:
    =FILTER(A1:B, ISODD(SEQUENCE(ROWS(A1:B))))

The ISODD function offers a cleaner and more intuitive solution.

Final Thoughts

While the ODD function may not be a cornerstone of data analysis, it’s a handy tool for specific use cases, like rounding up numbers to the nearest odd integer or working with odd-numbered datasets. Paired with functions like FILTER and ARRAYFORMULA, it becomes even more versatile.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.