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.

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Hyperlink to Jump to the Last Used Row in Excel

In a vertical range, you can create a hyperlink to jump to the last...

Find the Last Used Row’s Last Value Address in Excel

In a large vertical dataset in Excel, how do you find the cell address...

Find the Last Used Row Number in Excel

When working with large datasets such as sales records, purchase data, or bills of...

More like this

Filter the Bottom 10 Items in a Pivot Table in Google Sheets

This tutorial explains how to filter the bottom 10 items in a Pivot Table...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

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.