How to Divide in Google Sheets

You are a sales manager and need to get the average sales per transaction. In Google Sheets, you would divide the total sales column by the number of transactions column to get the average sales for each transaction.

Dividing numbers in Google Sheets is easy. It’s as simple as using a division formula into a cell (enter the numbers and apply the division operation directly.) or using the DIVIDE function.

In this tutorial, we will learn all the possible ways to perform divide in Google Sheets. So let’s get started…

Divide with the Division Operator

The easiest way to write a formula to divide is to use the division operator:

=A1/B1

1. Equal Sign (=): You need to start a formula using the Equal Sign. It tells Google Sheets that the cell has a formula rather than a normal value.
2. Dividend: The number you want to divide. In the formula =A1/B1, A1 has the dividend, which is 200. It’s the quantity that you are dividing up.
3. Division Operator (/): This operator is used to perform division. It tells Google Sheets to perform the calculation. It goes between the dividend and the divisor.
4. Divisor: This is the number by which you divide the dividend. In =A1/B1, B1 has the divisor, and the value is 10. It’s the number of parts you divide the dividend into.

You can also enter the values directly into the formula besides using the cell reference.

Divide with a Zero

When you try to divide a number by zero in Google Sheets, you get an error represented by #DIV/0! (Function DIVIDE parameter 2 cannot be zero). This error is Google Sheets’ way of telling you that your calculation has a fundamental mathematical problem.

To handle this, you can use functions like IF to check if the divisor is zero before doing the division, which can prevent the error.

For example, you can use the formula =IF(B1 = 0, “There’s a 0″, A1/B1) to display ” There’s a 0″ if the divisor is zero, avoiding the #DIV/0! Error.

Using the Divide Function to Divide Numbers

In Google Sheets, there is a straight way to divide numbers using the DIVIDE function. In this function, =DIVIDE(number1, number2), where number1 is the number you want to divide (Dividend), and number2 is the number you divide by (Divisor).

=DIVIDE(dividend, divisor)

• Dividend: This is the number you want to divide. It’s the total amount or quantity that you wish to divide.
• Divisor: This is the number by which you divide the dividend in the first argument. It determines how many parts the total will be divided into.

To divide the number from the cell A1 and B1, you need to use the formula below.

=DIVIDE(A1,B1)

While using the DIVIDE, dividing the dividend with a zero will also get the same #DIV/0! (Function DIVIDE parameter 2 cannot be zero). To handle this error, you need to use a function like IFERROR.

=IFERROR(DIVIDE(A1,B1),"There's an Error")

Create a Custom Function for Divide

The app script below is a simple custom function you can use directly on your Google Sheets, just like any other function. It divides two numbers and handles the divide-by-zero error.

The syntax of this function is the same as the DIVIDE function: = cDivide(dividend, divisor)

/**
 * Divides two numbers and returns the result or an error message if division by zero is attempted.
 *
 * @param {number} dividend The number to be divided.
 * @param {number} divisor The number by which to divide.
 * @return The result of the division or an error message if the divisor is zero.
 * @customfunction
 */
function cDivide(dividend, divisor) {
  // Check if the divisor is zero
  if (divisor === 0) {
    return "Cannot divide by zero"; // Error message for division by zero
  } else {
    return dividend / divisor; // Perform division if no error
  }
}

To enter this App Script, Go to Extensions > Apps Script. Save the script with a name, then close the Apps Script tab. After that, once you return to your sheet, you can use this function just like any built-in function.

As I mentioned, this function checks if the divisor is zero and returns a custom error message if it is true. Otherwise, it performs the division like a normal function.

Divide Numbers from a Column in One Go

Let’s say you need to divide numbers from a range of cells. In this case, you can write a formula using the ARRAYFORMULA. To divide values from the range A1:A10 by corresponding values from the range B1:B10, you can use the formula:

=ARRAYFORMULA(A1:A10/B1:B10)

In the formula, you have dividend values in the range A1:A10 and the divisor in the range B1:B10. When you enter this function in cell C1, it returns the result in the range C1:C10, dividing numbers from both ranges.

Cell Reference Vs. Hard Values

Entering values directly into a formula, like =10/2, makes your calculation fixed and inflexible unless you manually edit the formula. Using a cell reference, like =A1/B1, updates your formula if the numbers in cells A1 or B1 change, making it more flexible and dynamic for calculations.

When working with a large set of data and performing a divide, sometimes it’s tough to identify errors, so the best way is to use a formula to help you handle the error. Otherwise, you can use conditional formatting to highlight the cell where you have errors so that you can identify them and then correct the values.