In this tutorial, I will share with you about the math in Google Sheets functions of add, summation, subtraction, multiplication, division, the square and square root with examples. I hope after finishing this tutorial article, you will know how to do all types of math in Google Sheets.
TeqTog is a hub for tips and tutorials for working with Google spreadsheet. TeqTog can give you access to loads of tutorials, guidelines, tips and ideas to make your experience with Google spreadsheet comfortable and time & goal-efficient.
Our only objective is to make sure that you can stand out at work by submitting reports on time; based on analysis and complex calculations- which can be achieved if you know your way around Google Sheets.
Mathematical Formulas in Google Sheets
If you want to add A5 and A6 cell, then the add formula will be based on cell reference. It will be:
You can Add numbers (for example you want to add 52 and 48) directly in a cell without using cell references, with this formula.
ADD Function: You can use ADD function to add as well. Formula will look like:
It’s up-to you and your work, any of these 3 you can use to add. To know more with live example on how to Add in Google Sheets continue reading.
Google Sheets Sum Formulas
SUM Function: If you want to sum a range of cell like from cell C1 to C5 then you can use this formula to sum.
You can use SUM function as well in the condition when you want add different cell from different rows. For example you want to sum A1, C1, D2 and H5 you can do that with SUM function. In that case Formula will be:
You can use SUM function by mixing both case above the cell range and different cell from different rows as well. In that case formula will be like:
Like Add formula you can use subtraction formulas based on cell reference.
Subtraction without any cell references, directly using numbers.
MINUS function: Using MINUS function you can do subtraction.
Multiplication Formulas in Google Spreadsheets
Multiplication formulas based on cell reference.
Multiply without any cell references, directly using numbers.
MULTIPLY function: Using MULTIPLY function you can do multiplication.
Division Formulas in Google Spreadsheets
Division formulas based on cell reference.
Division without any cell references, directly using numbers.
DIVIDE function: Using DIVIDE function you can do division.
Google Spreadsheets Square Formulas
Square formulas based on cell reference.
Square without any cell references, directly using numbers.
Square formulas mixing both case above
- Square: (A1^2)
- Cube: (A1^3)
- To the power four: (A1^4)
- To the power five: (A1^5)
POWER function: Using POWER or POW function you can do square.
=POWER(4,2) or =POW(4,2)
Square root Formulas
=SQRT(“cell that has the value of which I want the square root”) means
How to Put a Formula in Google Sheets
We depend on Google Sheets to process huge volumes of numerical data and perform various calculations. While working on Google Sheets, you should be able to create mathematical formulas on Google Sheets which will execute those calculations. I will focus particularly on mathematical formulas which will perform additions, subtraction, multiplication and division for you.
Learning to create a formula which will help you carry out these calculations will not only save time for you, but, also allow you to get reliable results.
To begin with, I will show you how to put a formula in Google Sheets on the basis of cell reference.
Using Cell Reference
Using a cell reference to create a formula simply means linking a cell address in our formula.
The benefit of using a cell reference to perform a calculation is that, you can change the numerical value in your given formula and your spreadsheet will automatically recalculate considering the new value. Therefore, you can modify your values as many times as you need to, without having to modify the entire formula.
Google Sheets uses mathematical operators to perform calculations. Them being the following:
- A plus sign for adding (+)
- A minus sign for subtracting (-)
- An asterisk sign for multiplication (*)
- A forward slash sign for division (/)
- A caret sign for exponents (^)
Point and Click to Reference the Cell
In addition, you can reference to a cell by simply pointing your cursor on that cell and clicking on it; instead of typing the cell address manually.
How to Create a Formula using a Cell Reference in Google Sheets
1. To create a formula in Google Sheets using cell reference, first you have to have a set of data. Then, you should click on the cell in which you want the answer to your calculation.
2. Type in the equal sign in that cell.
3. Type the cell address you want to include in your formula. In this case, they will B1 and B2. When you link a cell in your formula, a dotted border will appear on that cell.
4. Give the operator you need. For instance I may want to find out the difference between the revenue from January and February. Therefore, I will use the minus sign.
5. Press the “Enter” key and you will get the result.
I can change the numbers in the referenced cells and get the results based on the new data.
Functions and Operators Difference
An operator determines the type of calculation that is to be performed on a set of values. As discussed there are primary arithmetical operators in Google Sheets which are the:
- Addition (+)
- Subtraction (-)
- Multiplication (*)
- Division (/)
- Exponent (^)
You can use a single operator or a combination of operators, depending on the kind of calculation you need to do.
In simple terms, a function is a calculation that is pre-determined. What this means is, instead of having to manually build your formula every time you need to do a calculation, you can just select the relevant function and Google Sheets will know what to do on your values. Thereby, saving you a whole lot of time and inconvenience.
For instance, if you were to add, let’s say twenty cells with values, you would have to manually formulate this:
However, with the sum function available in Google Sheets, you can just apply that on your set of values to do the same calculation: “=SUM (A1:A20)”
Order of Operations in a Spreadsheet
If you need to do a calculation on your spreadsheet using more than one operator; then you need to know the standard order of operations in your input.
In general, your spreadsheet will follow a standard order of operations to perform a calculation.
- Parenthesis – Your spreadsheet will prioritize parenthesis over the usual order of operations. Therefore, you can use parenthesis to make sure certain operations and performed first.
- Reference operations.
- Exponentiation – (^)
- Logical comparisons such as “less than” (<), “greater than” (>), “equal to” (=), “true” or “false”.
Plain Numbers and Cell Reference Difference
You can format your numerical values into plain text using the “Format” option from the top menu bar in your Google spreadsheet. By doing so, you will only get the figure from that cell if you copy and paste it and not the formula behind that figure.
Similarly, if you link a cell address in another cell, Google spreadsheet will display the formula each time you click on that cell.
Introduction to Google Sheets Math
If I am going to teach you about the numerous functions in Google Sheets, I should introduce Google Sheets itself to you first.
So what exactly is Google Sheets?
What is Google Sheets?
Google Sheets is spreadsheet program that is created, used and saved online. It is a part of the Google Docs Editors suite and Google had offered this completely free. The other elements of the Docs Editors suite include, Google Sites, Google Slides, Google Keep, Google Forms and Google Docs.
Google Sheets was officially made known in the year 2010; after it went through a couple of launches, re-launches and acquirements.
Where is Google Sheets Available?
Google Sheets is available as a website application, mobile app for Android, iOS, Blackberry and Windows and as a desktop app via Google’s Chrome OS.
Pros of Using Google Sheets
You should be familiar with some of the positive attributes of Google Sheets which make it so popular and usable.
- Google Sheets is really easy to use.
- One of the most significant advantages of Google Sheets is that more than one user can operate the spreadsheet and make modifications on it; in real time.
- In simple terms: Google Sheets is user-friendly. How? The icons and menus for the different functions maintain simplicity. Therefore, the user will not be confused or frustrated from having to work through so many different features and their options.
Cons of Google Sheets
- Google Sheets is not capable of handling more than a certain volume of data.
- Google Sheets may not be able to offer so many options for diagrams; like Microsoft Excel can.
- Functions in Google Sheets tend to be over-simplified at times. This may be inefficient for people from professions like logistics.
If you feel like learning more about the advantages, disadvantages and functions of Google Sheets and the primary differences between Google Sheets and Microsoft Excel; make sure to check out our Microsoft Excel VS Google Sheets article, where I covered the topic in detail.
Alright now, let me get started on discussing the primary functions in Google Sheets starting with the “Summation”.
How to Add in Google Sheets
One of the mathematical calculations you can do in Google Sheets is addition. Here is how you can perform addition on values in your Google spreadsheet.
1. You should have a set of data. Now, click on the cell in which you want your result.
2. Type in the equal sign.
3. As I have showed you earlier in this tutorial, refer to the cells with the values by either selecting those cells through clicking on them or typing in their cell address manually.
4. Give your mathematical operator. In this example, that will be the plus sign standing for addition.
5. Press the “Enter” key on your keyboard and you will get the results for your addition.
How to Sum Columns or Rows in Google Sheets
In my example below, I have a set of data that shows the number of sales that took place in each of the first four months; for each branch of a particular shop.
If I want to find out the total sales for each month but, also, the total sales for each branch; I will have to add the figures in each column, as well as the figures in each row.
I am going to start with the month of January.
So, the syntax for my formula will be as follows:
- Any and every formula for any function in Google Sheets start with the equal sign.
- “SUM” is my function I want my spreadsheet to perform.
- The first bracket is the first parenthesis.
- B2 is the cell which I want my spread sheet to start adding from.
- B5 is the cell till which I want it to add up.
- Second bracket is the second parenthesis.
Since all of the cells which I want to add up are arranged consecutively in my spreadsheet, I can just use the colon mark between the starting cell and the end cell.
- I will put down my formula in the total cell for January.
- If I press “Enter” now, I should get the totals for the month of January.
I can either give the input for the formula manually by typing “B2:B5” or, I what I can do is: after I type till “=SUM (“, I can just click on the first cell of my list and then simply drag my cursor till the cell which is the end of my addition list.
For the next cell (total for the month of February, I can either select the January total cell and copy it using the right-click menu and then paste on the February total cell. Google Sheet will paste the formula on the February total cell; however, use the values specifically for the February month instead for January.
For the rest of the cells, I can repeat the same formula and I will get the totals for each month, as well as, each branch of the shop.
So far we have seen that, all of the cells which I want to add up are all aligned consecutively. In cases where this is not so, what I can do is:
I can select the first cell then give a comma, then another, then a comma and then the other cell and, so on.
Moreover, suppose some of the cells in my addition list, are in consecutive order, while the others are not. Then I can give my input as shown:
=SUM (A1, B2:B8, C7, D6)
And there you go. That pretty much covers the summation function in Google Sheets and how you can use it.
Now, we can move onto the next section of this tutorial, which is “Subtraction in Google Sheets”.
How to Subtract in Google Sheets (3 Easy Methods)
Subtraction in Lists of Values in Google Sheets
Alright so, suppose that I have some kind of monthly income and I need to deduct all of my expenses. This is shown in the example as shown below:
Okay so here, I need the total of my expenses and that have that deducted from my monthly income of 50,000 (in whichever currency you prefer).
1. At first, I will add up all of my expenses by using the sum formula. If you remember my guidelines for using summation, you would know that I need to type in =SUM(B2:B7) in order to get the total expenses.
2. Now, I need to subtract that total from cell B1 (because that is where my monthly income is given).
Therefore, the syntax for my overall formula will be:
What this implies is, I am subtracting the sum of all my expenses (SUM of B2 to B7) from my monthly income (cell B1).
3. I should get the result for my net income if I press “Enter” for the given formula.
Another way in which I could have achieved the result for my net income is if I had typed in the figures for each expense as a negative figure. Afterwards, I would have simply applied the formula for summation starting from cell B1 (monthly income cell) till cell B7 (end of the expense list).
What this would have done is, Google Sheets would have added all of the figures and, we all know that if we add a positive figure and a negative figure- the negative figure gets deducted by default.
Going about it this way, will give me the same results.
Subtraction between two Figures in Google Sheets
If you want to perform subtraction between just two numbers in Google Sheets then that is much simpler.
Assuming that I have the ages of two people and I want to find out the difference in their age:
The syntax for the subtraction function will be,
All I have to do is, just put my equal sign first as usual, then subtract the two numbers exactly like I would if I were doing simple subtraction on paper – by putting the little “minus” sign between the two figures.
Except in the case of Google Sheets, I will select the two cells instead of the numbers.
And I will get the difference in their ages if I just press “Enter”.
Another Method is Subtraction by MINUS Function
By using MINUS function in your formula you can do same thing the subtraction in google sheets. Simply follow the given steps to use this MINUS function:
1. The basic formula will be look like this:
Here X=1,2,3……. n and Y=1,2,3……. n and
ValueX is the subtrahend and ValueY is the minuend.
2. Suppose, you want to subtract I3 and I4 cell values. No you have use this formula where you want to show your subtraction results. Like:
So there you go. All the basics you need to know to do subtraction in Google Sheets.
Moving on to the next mathematical function in Google Sheets would be Multiplication.
How To Multiply in Google Sheets
For learning to do multiplication on Google Sheets, I have an example of a list of items bought from a grocery store. Now, suppose all of those items were bought in quantities of more than one. Therefore, to know the total bill of my grocery shopping, I obviously have to multiply each item with their price and the number of quantities they were bought in.
1. To begin with, I will select the first cell, which is cell B2 (this has the price per unit for carrots) and, the second cell, which is C2 (this is the quantity of the carrots that were bought).
To select the cell, I will have to simply select the cell using my mouse.
2. Afterwards, I will multiply these two cells. The syntax for the multiplication function will be:
To get the star-marked symbol that represents multiplication, you need to enter the combination of keys: Shift+8
Therefore, the syntax if broken down a bit will be:
=B2 (Shift+8) C2
By entering this formula in the cell D2 (the cell to display total price for each item), I will get the results for my multiplication.
You can easily get the rest of the results, by selecting the first result of your multiplication (D2) and drag your cursor till D6. By doing as such, you Google Sheets will return the results for all of the multiplication performed between the rest of the price per units and their quantities.
You can also chose to add up the total prices for each item using the summation function, to know the total money spent on your grocery shopping.
Okay so there you have it. All you need to know for the next time, you need to do any kind of multiplication on Google Sheets.
We can now head on to the next function on our list for my tutorial on math in Google Sheets; and that is, how to do division in Google Sheets.
How to Divide in Google Sheets
So in this section of the article, I will try to teach you how to divide using Google Sheets.
For this example, consider that I have a few plates of cookies I want to distribute among classes in a school. So the set of data would look like the one show below:
This is a fairly easy example for you to understand.
1. First, I will select the cell in which I want my results to be. For this example, that cell will be D2 (For Each Student- to see how many cookies each student will receive).
2. Then I will give the input for my division formula. The syntax of my formula will be as follow:
- Equal sign has to be given before any formula is applied in Google Sheets.
- B2 is the cell which contains the value I want to have divided.
- The forward slash sign represents the division being taken place.
- C2 is the cell which contains the value with which I want to divide the value in cell B2.
To get the forward slash symbol for performing the division function, you need to select the key that has the question mark on it on your keyboard.
So the detailed syntax for the formula to use the division function would be as follows:
=B2(key for the question mark)C2
3. By entering this formula in D2, I will be able to see the results for the number of cookies available for each student.
Now, I can choose to either continue to find out the number of cookies for the rest of the group of students, or, I can simply select the cell with the first answer (and that is cell D2) and drag my cursor till the end of my division list.
By doing so, Google Sheets has considered the specific values for each row, but with the same division formula.
I hope this tutorial will help you the next time you need to perform division in Google Sheets.
And we are at the last but definitely not the least, section of our article on math in Google Sheets and that is, the square in Google Sheets. So, let us get to it, then!
How to Square in Google Sheets
This is something that is really simple in Google Sheets: the square!
What is a Square?
First of all, what does squaring a number mean?
Squaring a number means multiplying that number by itself. The square of 6, for instance, is 6 times 6 equaling 36.
Google Sheets has a number of mathematical formulas that will allow you to square numbers.
I will show you a few ways in which you can square numbers in Google Sheets. Then let us get right started!
How to Square Numbers in Google Sheets through Multiplication
Well, the easiest way you can square a value in Google Sheets is by multiplying a number by itself, using the multiplication function that I have shown you earlier in this article.
As you can see, I have only put down a random value (7) and in the next cell I have applied the multiplication formula (A1*A1). This will perform the multiplication 7 times 7 and then you will have the square of seven in the next cell.
As an alternative to this, I also could have manually given the input “= 7*7”, however, by doing so, I would not have had the liberty to replace 7 in the first cell with any other number and still get the same multiplication results in the next cell.
Squaring a Number in Google Sheets through POWER ()
Using the POWER () function is a much more sophisticated approach to squaring numbers in Google Sheets. You should know that Google Sheets does not own a local square function, but, this POWER () function does the just the trick.
The syntax of the POWER () function goes like this:
- We give the equal sign at the beginning of every formula in Google Sheets.
- Power is the function I am applying.
- The first bracket is the first parenthesis.
- Base is the value I want my spreadsheet to square.
- Exponent is the value of the power of the square.
- The second bracket is the second parenthesis.
Basically, in pen and paper the function of square would look like: base ^ exponent.
So, the square of 7 would be 7^2; because, I am multiplying the number 7 with 7 two times (meaning that there are two sevens being multiplied together).
In my spreadsheet, I would type in =POWER (7, 2)
Benefits of Using the POWER () Function in Google Sheets
Using the POWER () function in Google Sheets serves a great advantage. You can square your value in your spreadsheets with any power. What this means is that, you can change the value of your power and Google Sheets will multiply your number with that number itself – as many times as the value you have given to the power.
Suppose, if I wanted to multiply 7 with 7 5 times, I can just give the input
What this implies is that I have multiplied 7 with 7 with 7.
Learning about the SQRT in Google Sheets
The SQRT function in Google Sheets finds out the root of a number in Google Sheets.
What is Square Root of a Number?
The square root of a number is the value which has been multiplied with itself, to get the number being square rooted in the first place. Finding out the square root of a number is exactly the reverse of finding out the square of a number.
So, for instance, if the square of 7 is 49, then the square root of 49 is 7.
The syntax for this function would be:
1. Give the input for this formula. (For value within the syntax, I will select the cell with the value I would like to find out the square root of; which cell D4).
2. Afterwards, I will press “Enter” to get the square root of 49.
Squaring a Number in Google Sheets using the Base and Exponent
This technique is the most simple out of all of them.
So, let us consider that you want the square of 7 again. All you have to do is just type “= 7^2” in a cell in your spread sheet.
Notes on using the Base ^ Square in Google Sheets
While this method is the fastest and the most easy; you need to remember that once you hit “Enter” on “x^y” and get your results for the square, your spreadsheet will only display the result to you and you cannot view the values that made the basis of the formula.
For instance, in the above example, after I get the result “49”, I will only be able to view 49 and not “= 7^2”.
And I think that is all there is to it, when it comes to squaring numbers in Google Sheets.
And that I think, brings me to end of my tutorial on doing math in Google Sheets: summation, subtraction, multiplication, division and square.
I hope my step by step guidelines were helpful for you for the next time you will be performing any kind of math Google Sheets.
Remember, Google Sheets is spreadsheet that has a lot of useful features which will help you to perform various mathematical functions and all you need to do is learn about how to use them.
Best of luck!
You May Love to Read Our More Google Sheet Tutorials on: