Google Sheets Calculations: A Guide

Last Updated on February 24, 2024 by Jake Sheridan

In this guide, you will learn all about calculations in Google Sheets.

Basics Maths in Google Sheets 

Diving into the maze of Google Sheets calculations can seem like a…

…Wait a minute, I think AI wrote this intro. No human actually dives into a maze. You foolish robot, better luck next time. Let me write the rest with my fleshy human hands… 🤖

Whether you’re scheduling tasks, crunching numbers, or predicting trends, Google Sheets has got you covered.

Below you will find step-by-step instructions for calculating (almost) everything in Google Sheets.

1. How to add in Google Sheets

Here’s a detailed guide on how to perform addition in Google Sheets.

Adding Numbers with an Operator

The simplest way to add numbers together is using the addition operator or plus sign (+).

For example, to add the numbers 15 and 23 we’ll use the formula =15+23, returning a value of 38.

You can add even more cells to the formula as long as you add a plus sign between each value.

Instead of typing the number itself in the formula, you can use cell references instead. This will allow you to create dynamic formulas where you can change the output without editing the formula.

In the example above, we’ve computed the result in cell B3 by adding cell references to cell B1 and B2.

If we edit the values in cell B1 or B2, the output in cell B3 automatically updates.

Using the ADD Function

While not as common or widely-used, Google Sheets supports adding two numbers using the ADD function.

The ADD function requires only two arguments and returns the result of adding both arguments.

In the example above, we used the formula =ADD(B1, B2) to find the sum of two given values. Do note that the ADD function only supports the addition of two numerical values and takes neither ranges nor more than two arguments.

Using the ArrayFormula to Add Ranges Together

We can use the ARRAYFORMULA function to add two columns together using a single formula.

In the example above, we used the formula =ARRAYFORMULA(A8:A10+B8:B10) to add each pair of values from Col1 and Col2.

The ArrayFormula method is particularly useful for efficiently performing calculations on large datasets without the need for dragging formulas.

2. How to sum in Google Sheets

When adding a range of values, it can be tedious to continue using the addition operator. Luckily, Google Sheets includes a built-in SUM function where you can find the sum of one or more ranges.

Unlike the ADD function, the SUM function can accept ranges as input and can add two or more arguments together.

Here’s a step-by-step guide on how to use it:

Step 1

First, select an empty cell where you want to display the result of the SUM function.

In this example, we’ll find the sum of the range B2:B11 and output it in cell B12.

Step 2

To find the sum of our selected range, we’ll add it as an argument to our SUM function.

Step 3

Hit the Enter key to evaluate the function.

Step 4

The SUM function also supports adding multiple ranges. Simply add a new range by separating each range with a comma.

3. How to subtract in Google Sheets

Here’s how to find the difference between values in Google Sheets.

Subtracting Numbers with an Operator

The easiest method to subtract values in Google Sheets is by using the subtraction operator “-”. For example, we can find the difference between 20 and 15 by using the formula =20-15.

For example, to subtract 15 from 23, we’ll use the formula =15+23, returning a value of 5.

We can also subtract values by providing cell references.

In the example above, we used the formula =B1-B2 to find the final price of an item by deducting the discount (B2) from the original price (B1).

Using the MINUS Function

We can also use the MINUS function to subtract values. The first argument refers to the minuend or the number to be subtracted from. The second argument is the number to subtract from the first argument.

Using the ArrayFormula to Find the Difference Between Ranges

We can also subtract two ranges of the same size. First, type the string “=ARRAYFORMULA(“ to start a new array formula. Next, type the range you want to subtract from followed by a “-” followed by the range to subtract.

In the example above, we created an ARRAYFORMULA to subtract the range B7:B9 from the range A7:A9.

4. How to multiply in Google Sheets

Multiplying values in Google Sheets can be achieved through various methods. Below are step-by-step instructions for each possible way to multiply values:

Direct Multiplication of Two or More Numbers

We can directly multiply values in Google Sheets using the multiplication operator. In Google Sheets, the asterisk character (*) can be placed between values to specify we want these values multiplied together.

For instance, to multiply 5 by 3, type =5*3.

We can use the operator multiple times in a single equation.

We can also multiply values in our spreadsheet by providing cell references.

In the example above, we multiplied the given item’s unit price (B1) with the quantity ordered (B2) to calculate the total price to charge the customer.

Using the PRODUCT or MULTIPLY function

The MULTIPLY function returns the product of two numbers.

For example, we can use the formula =MULTIPLY(B1,B2) to multiply the values in cells B1 and B2.

However, the MULTIPLY product only supports two numbers. If you want to multiply three or more values, we recommend using the PRODUCT function instead.

For example, the formula =PRODUCT(2,3,4) is equivalent to the equation =2*3*4

Multiplying a Range of Cells by a Single Number (Using Array Formula)

We can use an array formula to multiply a range of cells by a single number. This can be useful in a variety of situations such as applying discounts or converting from one unit of measurement to another.

In our example above, we used the formula =ARRAYFORMULA(A6:A10*0.93) to convert our prices from USD to Euro.

Multiplying Two Arrays

In some cases, you may want to multiply two columns or arrays together. For example, you may have one column with the price per unit and another column with the quantity sold. By multiplying these columns together, you now have a new column showing the total sales revenue for each product.

In the example above, we used the formula =ARRAYFORMULA(B13:B16*C13:C16) to multiply the unit cost column with the quantity column. The formula returns a new array of values representing the total sales revenue of each item.

5. How to divide in Google Sheets

Dividing numbers in Google Sheets is a straightforward process. Here’s a step-by-step guide:

Using the Division Operator

We can directly divide values in Google Sheets using the division operator. In Google Sheets, the forward slash character (/) can be added between values to specify we want to divide the preceding value with the value after the operator.

For instance, to divide 100 by 5, type =100/5.

We can also divide values in our spreadsheet by using cell references in our formula.

In the example above, we calculated the average daily revenue by dividing the total revenue (in cell B1) with the number of days in our date range (found in cell B2).

Using the DIVIDE and QUOTIENT functions

Google Sheets allows you to perform the division operation using built-in functions, namely the DIVIDE function and the QUOTIENT function.

Here are the steps for using both, along with an explanation of their differences:

Using the DIVIDE Function

The DIVIDE function is equivalent to the “/” division operator as they both return the result of one number divided by another number. The first argument refers to the dividend (the number to be divided) and the second argument is the divisor (the number to divide by)

For example, if we want to divide 100 by 3, we can use the formula =DIVIDE(100/3).

Similar to the division operator, DIVIDE will output a #DIV/0! error if the divisor is equal to 0.

Using the QUOTIENT Function

The QUOTIENT function also divides two numbers, however the function only returns the integer portion.

For example, the formula =QUOTIENT(10,4) will return 2 rather than the actual quotient of 2.5.

This output could be useful when handling situations where we want to disregard fractional data. For example, when calculating inventory-related metrics, you may want to only consider complete products rather than partial products.

6. How to square numbers in Google Sheets

Squaring numbers refers to the operation where you multiply a number by itself. For example, squaring 4 returns 16 since 4×4 =16. Squaring is a useful operation to know since it comes up in a variety of mathematical and real-world applications.

To square numbers in Google Sheets, you can either use a simple arithmetic formula or the POWER function. Here’s a step-by-step guide for both methods:

Using an Arithmetic Formula

In mathematics, squaring a number is equivalent to raising a given number to the second power.

This operation can be expressed in Google Sheets as n^2, where n is your given number. The exponent symbol (^) signals that we want to raise a number to the power of another number.

In our example above, we used the formula =5^2 to determine that five squared is 25.

Using the POWER Function

We can also use the POWER function to find the square of a number.

The POWER function follows the following syntax:

=POWER(base, exponent)

Let’s look into what each input is:

  • base refers to the number to raise to a specified power
  • exponent refers to the exponent to raise the base to.

For example, we can use the formula =POWER(5,2) to find the square of 5.

7. How to square root numbers in Google Sheets

In mathematics, the square root of a number is a value that, when multiplied by itself, returns the original number. For example, the square root of 9 is 3 since 3×3=9.

To calculate the square root of numbers in Google Sheets, we can use the SQRT function.

The SQRT function returns the positive square root of a positive number. The function requires a single value which must be positive. If a negative number is provided, SQRT will return a #NUM! Error.

For example, we can use the formula =SQRT(144) to find the square root of 144.

We can also use the POWER function to find the square root. This is because raising a number to a fractional power is equivalent to finding the root of the number. Exponents and roots are inversely related operations, similar to the relationship between multiplication and division.

To find the square root using the POWER function, we’ll need to set our exponent to 0.5 (or ½). By converting 2 to its multiplicative inverse of ½, we are essentially creating an inverse operation of finding the square.

In the example above, we used the formula =POWER(144,0.5) to find the square root of 144.

8. How to calculate equations in Google Sheets

Google Sheets allows you to use operators more than once in a single formula. This is useful when you need to calculate certain equations in your document.

To test this out, let’s try using the built-in operators in Google Sheets to calculate simple interest.

Suppose we want to find out the total interest earned after 10 years from a loan of $5,000 with an interest of 4.5%. Let’s assume that the loan follows simple interest.

Simple interest is computed using the following formula:

Principal x Rate of Interest x Time Period.

We’ll start preparing the data we need for our equation (principal, rate of interest, and time period). We’ll input the data in separate cells in our spreadsheet.

For this example, place the principal amount in cell B1, the interest rate in cell B2, and the number of years in the time period in cell B3.

Next, we’ll click on the cell where we want the simple interest calculation result to appear.

In this empty cell, we’ll enter the formula to calculate simple interest: =B1*B2*B3.

 

Press Enter to evaluate the equation.

In our current example, we find out that we would have earned $2,250.00 after 10 years.

9. How to create equations with multiple operators

More advanced equations require two or more operators in a single formula. In this section, we’ll look into a specific example of using a Google Sheets formula to calculate one of these types of equations.

Calculating sales growth rate in Google Sheets is a valuable metric for assessing business performance over time.

The sales growth rate can be calculated with the following formula:

(Current Period Sales – Prior Period Sales)/ Prior Period Sales) * 100

This equation requires a Google Sheets formula that uses multiple operations (subtraction, division, and multiplication).

We’ll start preparing the data we need for our equation (prior period sales and current period sales).

In our example, we want to find the quarterly sales growth rate given the sales figures in column B of our quarterly sales table.

To start, click on the cell where you want the sales growth rate to be displayed.

We’ll select cell C3 to have our first equation to calculate the sales growth rate from the first to second quarter. Note that we’ve left cell C2 blank since we do not have data from before Q1 of the current time period.

We’ll then enter the formula for calculating the sales growth rate: =((B3-B2)/B2)*100. After evaluating our formula, we found out that our sales grew at a rate of 6.86% in Q2.

We can drag the formula down to find the sales growth rates of the succeeding quarters.

 

Summary

Hopefully this guide has given you an overview of the different calculations Google Sheets can handle.

What’s Next?

Explore some of the other useful resources on Sheets for Marketers: