Coupler.io Blog

A Complete Guide on How to Do Calculations in Google Sheets

  • Piotr Malek

    • The calculations in Google Sheets are not limited to basic arithmetic operations like extraction, multiplication, and so on. This spreadsheet app provides more options to calculate your data for digital marketing, financial modeling, or statistical analysis. Let us dive right into things and start off by understanding the most basic concept.

    How to make calculations in Google Sheets?

    Remember how you used to perform math calculations? You can make calculations as easily in Google Sheets using formulas. Formulas in Google Sheets execute calculations on spreadsheet data. You can use formulas for simple calculations like addition and subtraction, as well as more complex ones like wage deductions or test averages.

    One of the biggest advantages of using Google Sheets is that its formulas are dynamic, meaning that if you alter the data in the spreadsheet, the result will be recalculated immediately wherever it occurs without you having to re-enter the formula.

    How to make calculation rules on Google Sheets

    The process of making calculation rules is very simple. 

    How do I perform simple calculations in Google Sheets?

    To perform simple calculations in Google Sheets, first we will create a basic formula. In our sample formula, we will first add numbers 10 and 5 and then subtract 3.

    Note: In a Google spreadsheet, you should always begin by entering the equal sign in the cell where you wish the answer to appear.

    Note: By using the cell reference in the formula, the answer will automatically change if there is a change in the values in the cell.

    Another way to add cell references is by using a feature known as point and click. This feature allows you to click and select the cell of the data and add its reference to the formula. 

    How to have Google Sheets repeat a calculation

    Most of the time, you have to repeat a calculation or apply your function to multiple rows or columns. And it’s very simple to do it on Google Sheets.

     Here are the steps to repeat the calculation:

    This is the method for beginners who work with small data sets. If you deal with bigger ones, you’d better use ARRAYFORMULA in Google Sheets. 

    How to make horizontal calculations in Google Sheets

    In the last calculation, we just used cells of Column A to perform a vertical calculation. But we can use multiple columns to perform horizontal calculations in Google Sheets. Let’s take an example where we’ve calculated the total expenses of a company. 

    The rules for vertical and horizontal calculations are the same in Google Sheets.

    Google Sheets percentage calculation

    When performing tasks in Google Sheets, you’ll frequently need to use percentages to calculate things like sales tax, test results, inflation rates, and a variety of other things.

    Google Sheets makes it plain and simple to calculate and format percentages with a few clicks and a simple formula.

    Formatting percentage

    You don’t have to multiply the answer with 100 in Google Sheets, simply because you can set the format of the cells to percent, and Google Sheets will do it for you.

    Google Sheets formula for percentage 

    The general formula for percentage is Part/Total. Let’s look at an example where we have to find the percentage of sales for each employee.

    How to make part of a cell calculation static in Google Sheets

    In Google Sheets, we use cell referencing to assist our calculations in finding the data they require. These values aren’t always set in stone; in fact, they’re not always known when the formulas are applied. Google Sheets will automatically change the cell address in the formula when you are applying it to multiple cells. To overcome this, you have to make the cell static in the formula. 

    We use absolute referencing to make part of the cell static. You can make one or both characteristics of the cell static using the absolute reference. That is, you can lock the column and/or row. Simply add a “$” before the column or row to accomplish this. Here’s how we go about it:

    $A$1 – Makes both column and row static

    $A1 – Makes column static

    A$1- Makes row static

    Google Sheet static calculation

    Let’s look at an example to see how to use the absolute reference to do a static calculation.

    If we try to copy the equation to find the percentage of other students, the formula will automatically adjust. This will be wrong as we want the cell reference of C1 to be static in the equation.

    To overcome the problem, we used absolute reference in the formula and made cell C1 static. 

    How to do an average calculation in Google Sheets

    The mean is the most fundamental sort of average. This is what most people mean when they say “average” without explaining what kind of average they’re talking about. As a result, the AVERAGE function in Google Sheets is utilized to calculate the mean.

    A function is a formula that Google Sheets runs on your data to perform calculations automatically. You can design a formula that performs computations on data entered in specific cells of the spreadsheet using functions. They provide a quick way to do calculations. 

    Let’s see how the AVERAGE function is used in Google Sheets.

    Google Sheets time calculation

    Calculating duration is a common activity for many people who keep track of their work hours on a spreadsheet. Record and time calculation in Google Sheets is very common. It is mostly used to keep track of work hours. However, in order to understand the final duration, the cells must be formatted correctly:

    The Google Sheets time calculation can be used to calculate age and dates as well.

    Google Sheets age calculation

    You can calculate the age of a given period in Google Sheets. This can be helpful to keep track of people’s birthdays, contracts, etc. 

    Use Google Sheets DATEDIF function to calculate age. You can use this function to calculate years, months, and days between two dates.

    Here are the unit arguments you can use in the DATEDIF function:

    Here is how to use the function in Google Sheets.

    It shows 5 years instead of 6 because 5 full years have passed between the two dates.

    =DATEDIF(B1,B2, "YM")

    We used the “YM” argument in the formula to calculate the months passed between the two dates after the completed years. In this case, 8 months have passed after 5 years. 

    “MD” argument in the formula specifically tells us the days left after the years and months.

    =DATEDIF(B1,B2,"Y")&"Years"&DATEDIF(B1,B2,"YM")&"Months"&DATEDIF(B1,B2,"MD")&"Days"
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
  "{{YEARS}} Years {{MONTHS}} Months {{DAYS}} Days", 
  "{{YEARS}}", DATEDIF(B1,B2,"Y")), 
  "{{MONTHS}}", DATEDIF(B1,B2,"YM")), 
  "{{DAYS}}", DATEDIF(B1,B2,"MD")

    The result:

    Google Sheets date calculation

    You can also calculate the days between two dates to keep track of the duration of projects, loan payment period, etc. To count the days between two days, we use the DAYS function in Google Sheets. The following are the steps to use the DAYS function.

    =DAYS(B2,A2)

    B2 is the cell address of the ending date.

    A2 is the cell address of the starting date.

    How to use Power Calculation in Google Sheets

    Google Sheets is an excellent tool for storing data and performing calculations. In addition to carrying out simple mathematical operations such as addition and subtraction, it can calculate exponents or power of number Google Sheets. 

    The easiest way to calculate power or exponent in Google Sheets is to use the power operator. The power operator or Caret is an upside-down V-shaped character on the keyboard and is used inside a formula to calculate exponent quickly. This is how to use it:

    You can perform the same calculations using reference cells, for example, the formula =A2^A3.

    Make your calculations dynamic with automatic data refresh in Google Sheets

    Before making calculations in Google Sheets, you need to import data. You can do this via copying and pasting datasets manually or exporting-importing CSV files. However, this can be very time-consuming if you need to refresh the data frequently, like every day. The obvious solution here is to automate data import from your sources to Google Sheets. 

    You can easily do this with Coupler.io. It’s a reporting automation solution that lets you schedule data exports from 60+ apps and data sources. Moreover, it’s available as a Google Sheets add-on to manage your imports right from your spreadsheet.

    You only need to select your source app in the form below and click Proceed.  

    You’ll be prompted to create a Coupler.io account for free and connect your data source account. Then select the data to load to Google Sheets. 

    Coupler.io also allows you to perform basic calculations on the go before the data hits your spreadsheet. You can sort and filter records, rename and edit columns, add custom columns, and even blend data from multiple sources.

    After that, you need to specify the spreadsheet and the sheet where to load your data. You can even specify an exact range of cells (e.g. B5:G10) for importing data that only fit into this range.

    The last step is to enable Automatic data refresh and configure the desired schedule. You can have your data refreshed up to every 15 minutes! 

    This means that your calculations in Google Sheets will always be up-to-date without any manual effort from your side. Here is what it looks like. We’ve imported inventory data from our Shopify store:

    In the spreadsheet, we create a new tab where, for each day, we’ll be automatically calculating:

    For the first field, we use the COUNTA formula. It returns the number of rows in the tab with orders that contain any values. So, basically, we get the number of orders we received. To reference a value of another tab, you can type in its name as shown below or simply type in a function name, open the brackets, jump to the desired tab, and select the value you wish to use.

    For sales and inventory, we use the common SUM() function that sums up all the numerical values in the selected range.

    As the importers run every day, the data is automatically calculated in my spreadsheet.

    How to get the number from a calculation in Google Sheets

    In some cases, you want to remove all the text or strings from a cell value and extract only the numbers from it. Google Sheets have a function that can remove the text and extract numbers. 

    =REGEXREPLACE(A1,"[^[:digit:]]", "") 

    The arguments of the REGEXREPLACE function are:

    Text: The cell address that contains the source or string from which you want to extract the numbers.

    Regular_expression: This specifies the part of the string or text that you’re looking for. And to extract numbers from text, we use this [^[:digit:]] regular expression.

    Replacement: is the text that will be substituted into the original text.

    Let’s put this formula into practice and see how, you can extract the numbers by using this function:

    And here are the results:

    Google Sheets iterative calculation

    Sometimes when you are repeating complex calculations in Google Sheets, a circular reference error can occur. To overcome this problem, Google Sheets provides a feature called Iterative calculation. 

    Iterative calculations are calculations that are performed repeatedly until a certain numeric condition is satisfied. Google Sheets uses iterative calculations to obtain formula solutions by repeating the same calculation with previous findings. 

    It can figure out the possibility of potential solutions by analyzing prior outcomes. There are some calculations that need to be circular, meaning the formula refers to its own cell, either directly or indirectly. 

    So, if your file contains a circular reference, the formula will become stuck in a loop, calculating over and over again in an attempt to discover a solution. 

    When iterative calculations are enabled, the Google Sheets file will stop calculating when a set number of rounds have been completed, and the circular formula has discovered a stable result.

    This feature is turned off by default and can be turned on from the settings according to your requirements.

    The pathway to the feature is:

    File> Spreadsheet settings> Calculation> Iterative Calculation.

    Google Sheets loan calculation

    Google Sheets provides inbuilt functions to carry out accounting calculations. The PMT function is the one used to perform loan calculations and is available for personal and business use.

    The PMT function can be implemented to calculate periodic payments for the loan. The formula syntax is the following:

    PMT(rate, number_of_periods, present_value)

    Let’s look at an example to see the function in action in this example.

    Suppose you took a mortgage loan for your house and the following are the details of it:

    In the function, we divided the rate by 12 because we are paying monthly and we gave a negative value to the loan since we are calculating the amount to pay back.

    $4219.28 is the amount you will be paying monthly for 180 months to return the loan.

    Google Sheets manual calculation

    One fallback of Google Sheets is that it does not allow choosing between automatic or manual calculations. All your calculations are updated automatically on change. The absence of the option to switch between manual and automatic calculation can cause some problems. Since it can automatically change your previous results if you make changes to your formula. 

    To counter this problem, we recommend you make a copy of your spreadsheet in a new tab and test whatever changes you want to make to the formula. Once you’re satisfied with the outcome, only then should you replace your original tab with the new version. 

    How to do an if/then calculation in Google Sheets

    When working on large spreadsheets, you may find yourself repeating the same calculations for multiple scenarios, but Google Sheets includes excellent tools to help you avoid this extra work. You can use If/Then statements to switch between alternative options if you want to update a Google Sheet when certain conditions are satisfied.

    It can help you save a lot of time by eliminating the need to switch between spreadsheets every time something changes.

    If/Then statements in Google Sheets refer to the IF function, which returns different values depending on the outcome of a logical test.

    The first output used in the logical test is TRUE. The second output is utilized if it is FALSE.

    Let’s take a look at how this works in practice:

    So, whenever you change the values in A1 and B1, the spreadsheet will check the values and report the results accordingly.

    Take advantage of the calculations in Google Sheets

    Summing up, Google Sheets is a powerful tool that enables you to carry out simple to complex calculations with ease through its inbuilt Google Sheets functions. It allows you to customize calculations to your work requirements, establishing itself as flexible and user-friendly. 

    To make your calculation dynamic, consider using Coupler.io to automate data exports from your sources to Google Sheets. This way, your calculations will be automatic and your reports will always display the up-to-date. And you won’t have to spend a minute on this.

    Automate data import to Google Sheets with Coupler.io

    Get started for free
    Exit mobile version