5 Ways to Divide in Google Sheets

Do you need to perform division calculations in Google Sheets? This post is going to show you exactly how to do that!

From simple to more complex use cases, dividing values is one operation you will come across often when working with Google Sheets. You should be familiar with the various ways to get it done.

There are a handful of approaches to dividing values in Google Sheets. The best method depends on the requirement at hand, efficiency, and convenience.

This post will explore all the ways to divide in Google Sheets. Get your copy of the example workbook to follow along.

Divide using the Divisor Operator

Mathematical operations have a symbol that serves the purpose of an operator. The slash / character is used as the division operator, and it is very simple to use for dividing items.

= 20 / 4

You can use it like the above formula to divide values inside a cell. This example divides the value 20 by 4 and returns 5 in the cell.

= A1 / B1

This operator can also be used with cell references. This example divides the value in A1 by the value in B1.

Order of Precedence When Dividing

The spreadsheet follows a certain order of precedence when using arithmetic operators.

This illustration provides insight into how the spreadsheet handles operators.

  • Any operation inside parenthesis always takes precedence.
  • If there are no parenthesis, then divisions are calculated first.
  • Multiplications take precedence after divisions.
  • Then any additions or subtractions are performed.

That is why the calculations in the illustration return different results.

It’s important to take note of this order of precedence when using operators to avoid unintended calculations.

To avoid any confusion, there are functions that can perform the task of arithmetic operators.

Divide using the DIVIDE Function

There is a DIVIDE function which is an elegant upgrade from the division operator. Both methods divide the same way and they will return the same results.

= DIVIDE ( dividend, divisor )

The DIVIDE function has two arguments.

  • dividend is the value to be divided.
  • divisor is the division unit.

In this example, we have the Sales and Price data. To find the Quantity sold, we simply divide the Sales values by their corresponding Price values.

= DIVIDE ( A2, B2 )

Enter the formula in the first empty cell in the Quantity column and drag down the fill handle to copy the formula to the empty cells.

You will also get the same result with the divide operator.

The DIVIDE function has one limitation. You can only divide two values because it only takes two arguments. Performing more than one division calculation means you will need to nest multiple DIVIDE functions.

But most of the time you will likely not need to divide between more than two values.

However, there are situations where you will need to divide between more than two values. For example, when converting seconds to hours. In this situation, you will need to divide by 60 twice!

Divide using the QUOTIENT Function

= QUOTIENT ( dividend, divisor )

The QUOTIENT function is another nifty division tool in Google Sheets. It is very similar to the DIVIDE function, and they both even have the same arguments.

The difference between both functions is that the QUOTIENT doesn’t return the remainder of the division operation while the DIVIDE function returns the remainder.

= QUOTIENT ( A1, B2 )

Dividing the Sales by the Price might return some decimals when dividing with the DIVIDE function.

The QUOTIENT function however removes the numbers after the decimals and returns only the whole number part.

Divide using IFERROR Function to Avoid #DIV/0! Error

Your division syntax can return errors sometimes.

You will get a #DIV/0! error when you divide by zero.

In more simple dividing operations with small rows of data, you can easily fix this. It’s a completely different case when you have thousands of rows of data.

You can troubleshoot your syntax and change the default error message returned if a value is divided by 0 using the IFERROR function.

= IFERROR ( value, [value_if_error] )

The IFERROR function is one of the logical functions in Google Sheets and another variation of the IF function. It checks for an error and returns a specified error value if there is an error.

The IFERROR function’s arguments are value and value_if_error.

  • value is the result to return.
  • value_if_error is result to return if the calculation in the value argument returns an error.

You can achieve the same results using the traditional IF function, but the IFERROR is the more efficient option because it uses fewer arguments.

This is how you can use the IFERROR function to prevent a #DIV/0! error from appearing in your data.

= IFERROR ( DIVIDE ( A2, B2 ), "-" )

In the syntax, the IFERROR function will run the DIVIDE syntax in the value argument, and when there’s an error in the operation, it will return a dash - instead of the default error message.

Without the IFERROR, this is how the results will appear.

You can use the IFERROR syntax with other methods of dividing as well.

Divide using an Apps Script

You can also create an app script to help you divide.

You can check out this article to find out more details on apps scripts in Google Sheets.

Suppose you have a list of values, and you want to divide each value by a constant. You can create an apps script that can help you achieve that without making any extra calculation columns.

function divide() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var selectedRange = ss.getActiveRange()
  var selectedValues = selectedRange.getValues()
  var selectedColumns = selectedRange.getWidth()
  var selectedRows = selectedRange.getHeight()

  var ui = SpreadsheetApp.getUi()
  var input = ui.prompt("Enter value to divide range by")

  for(i = 0; i<selectedRows; i++) {
    for(j = 0; j<selectedColumns; j++) {      
      if (input.getSelectedButton() == ui.Button.OK) {
        selectedRange.getCell(i + 1, j + 1).setValue(selectedValues[i][j] / input.getResponseText());
      }
    }
  }
}

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Divide')
      .addItem('Divide Range with Single Value', 'divide')
      .addToUi();
}

Copy and paste this syntax to your editor and run it. After granting the necessary permissions, reload your spreadsheet.

The script will create a new menu in the spreadsheet’s UI after reloading. This new menu item will allow you to divide a selected range by a constant value.

To use the script.

  1. Select the cells whose values you want to divide by a constant factor.
  2. Click on the Divide menu.
  3. Select the Divide Range with Single Value option in the submenu.

This action triggers a prompt.

  1. Enter a number in the text box.
  2. Click the OK button.

The script will divide each value in the range by the value entered in the prompt and return the result in the same cell, just like a paste special divide operation.

This way you don’t need to create an extra column of division calculations!

Conclusion

Doing basic arithmetic operations are very commonplace when working with Google Sheets.

It’s beneficial to know how to carry these operations out effectively. When you need to divide values, you can easily apply any of these methods described.

The divide operator can do all kinds of divisions you might need but you might just prefer to use the DIVIDE function to avoid any confusion around the order of operations.

If you’re only interested in the quotient of your division, then the QUOTIENT function will suffice.

The IFFERROR function will prevent from returning a #DIV/0! error.

With the Apps script solution, you can easily divide a whole range with a constant value without creating any redundant data.

What other division methods do you use? Let me know down in the comments section!

About the Author

Oluwaseun Olatoye

Oluwaseun Olatoye

Oluwaseun is a business intelligence analyst with expertise in Google Sheets and SQL programming language. He has worked with various businesses to make data-driven decisions. He enjoys helping others learn and grow.

Related Articles

Comments

0 Comments

Submit a Comment

Your email address will not be published.

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!