These are the countries currently available for verification, with more to come! Show - Sum formula in Google Sheets
- Subtraction formulas in Google Sheets
- Multiplication formulas in Google Sheets
- Division formulas in Google Sheets
- Square formulas in Google Sheets
- Square root formulas in Google Sheets
- Spreadsheet math: Functions Vs. Operators
- Mathematical spreadsheet operators
- Mathematical spreadsheet functions
- Order of operations in a spreadsheet
- Using plain numbers vs. cell references
- Doing spreadsheet math by using only numbers
- Spreadsheet math using cell references
- Using a mix of numbers and cell references to do math
- Applying formulas to multiple cells quickly
- How to add in Google Sheets
- Adding numbers
- Adding cells with numbers
- Adding multiple cells
- Adding multiple non-adjacent cells
- Using the ADD function to add
- How to sum in Google Sheets
- How to subtract in Google Sheets
- Subtract numbers
- Subtract cells with numbers
- Using the MINUS function to subtract
- How to multiply in Google Sheets
- Multiply numbers
- Multiply cells with numbers
- Using the MULTIPLY function to multiply
- How to divide in Google Sheets
- Divide numbers
- Divide cells with numbers
- Using the DIVIDE function to divide
- How to square numbers in Google Sheets
- Square numbers
- Square cells with numbers
- Square using the POWER function
- How to square root numbers in Google Sheets
- Square root numbers
- Square root cells with numbers
- How to calculate equations in Google Sheets
- Applying the equations to the entire column:
- How to create equations with multiple operators
- Pop Quiz: Test your knowledge
- Question #1
- Question #2
- Question #3
- Question #4
- Question #5
- Answers to the questions above:
United States Canada Mexico American Samoa Guam Northern Mariana Islands Moldova Guernsey South Africa China Israel Japan South Korea Taiwan Australia New Zealand Argentina Brazil Chile Venezuela
One of the most useful things that you can do with Google Sheets, is mathematics. Whether you are simply wanting to solve simple math problems, or whether you have calculations that need to be performed on a set of data, doing math in a Google spreadsheet is very easy if you know the right symbols to use.
- Type an equals sign in a cell (=)
- Type a number, or a cell reference (of a cell that contains a number)
- Then use one of the following mathematical operators
**+**(Plus),**–**(Minus),*****(Multiply),**/**(Divide) - Type another number or cell reference
- Press enter
In this article I am going to show you how to do math in a Google Spreadsheet, by using ordinary numbers and cell references… and I will also show you the difference between mathematical functions, and mathematical operators.
Add by using cell references Add numbers, without cell references Add by using the ADD function ## Sum formula in Google Sheets## Subtraction formulas in Google SheetsSubtract by using cell references Subtract numbers, without cell references Subtract by using the MINUS function ## Multiplication formulas in Google SheetsMultiply by using cell references Multiply numbers, without cell references Multiply by using the MULTIPLY function ## Division formulas in Google SheetsDivide by using cell references Divide numbers, without cell references Divide by using the DIVIDE function ## Square formulas in Google SheetsSquare by using cell references Square numbers, without cell references Square numbers using the POWER function ## Square root formulas in Google SheetsSquare root by using cell references Square root numbers, without cell references Get your free Google Sheets cheat sheet ## Spreadsheet math: Functions Vs. OperatorsIf you are new to using Google Sheets formulas, it can be very tempting to use the mathematical functions such as =Add, =Subtract, =Minus, =Divide… and these functions do work… but Some of these mathematical operators (listed below) are very intuitive, such as Plus (+) and Minus (-), however not all all super obvious, such as the symbol/operator multiplication, which you may think would be the letter "x"… but is actually an asterisk (*). For summing and square rooting, functions are required for doing this type of math problem instead of operators. ## Mathematical spreadsheet operators**Plus sign (+)**~ Addition**Minus sign (-)**~ Subtraction**Asterisk (*)**~ Multiplication**Forward Slash (/)**~ Division**Carrot (^)**~ Exponent
## Mathematical spreadsheet functions**=ADD(**~ Addition**=MINUS(**~ Subtraction**=MULTIPLY(**~ Multiplication**=DIVIDE(**~ Division**=SUM(**~ Summing**=POWER(**~ Exponent / Power- =
**SQRT(**~ Square Root
## Order of operations in a spreadsheetThe order of operations that is taught in normal math classes, also apply in a Google Spreadsheet. The easiest way to make sure that your mathematical formulas are solved in the order that you expect, is to use more parentheses to isolate your needed numbers/terms
**P**(Parentheses)**E**(Exponents)**M**(Multiplication)**D**(Division)**A**(Addition)**S**(Subtraction)
**=(6-3)+2^2/4*4-1**- =(3)+2^2/4*4-1
- =(3)+4/4*4-1
- =(3)+1*4-1
- =(3)+4-1
- =7-1
**=6**
## Using plain numbers vs. cell referencesWhile setting up math formulas in your Google spreadsheet, you can type numbers directly into the formula, or you can also refer to a cell that has a number inside of it…. or you can use a mix of the two where needed.
Whether you should type the numbers directly into the formula, or if you should enter the numbers into cells and then refer to those cells in your formula… all depends on your specific task. If you plan to change a number quite frequently, then it is best to use a cell reference. But if you are applying a constant in your formula that will not change, it may be better just to type the number in the formula. There are several examples of this shown below with images included, but here is a quick explanation of using cell references vs. plain numbers in mathematical spreadsheet formulas. ## Doing spreadsheet math by using only numbersFor example, if you wanted to do a simple math problem, using Google Sheets like a calculator, you could type something like the following into a cell:
This will display an answer of "9", in the cell. ## Spreadsheet math using cell referencesBut if you want you can also enter the numbers into cells, and do math by referring to those cells. If you entered the number "27" into cell A1, and the number "3" into cell B1, you could use the following formula in any other cell:
This will also display an answer of "9" in the cell. ## Using a mix of numbers and cell references to do mathIn many cases you will want to use cell references and numbers, like this.
This will also give an answer of "9", assuming that the number "27" is entered into cell A1. ## Applying formulas to multiple cells quicklyYou will probably want to be able to apply calculations in your spreadsheet to multiple cells easily and quickly, and so I want to show you two ways of doing this. (The final examples in this lesson will demonstrate both of these methods with an image included).
Click here to learn how to quickly copy formulas down an entire column.
## How to add in Google SheetsLet's begin with the different ways to add in Google Sheets. I'll show you how to add by using ordinary numbers, as well as by using cell references, and then I'll show you how to add more than two cells together.
## Adding numbersThe formula below does not have any cell references, and simply uses plain numbers to do math in Google Sheets, kind of like using a calculator. Pick any cell in your sheet, type the formula below, and ten press enter.
The cell should display an answer of "300", as shown below. ## Adding cells with numbersNow let's add by using cell references. Instead of typing numbers directly into our formula, this time we are referring to cells that have numbers inside of them, and telling the formula to add the numbers that are in those cells.
- Click on cell A1, then type the number "100", and then press enter
- Click on cell A2, then type the number "200", and then press enter
- Click on cell A3, then type "=A1+A2", and then press enter
- Cell A3 should now display an answer of "300"
Formula shown below: ## Adding multiple cellsYou can also add more than two cells together in your spreadsheet. Simply continue typing the cell references that you want to add, separated by plus signs, and then press enter when your formula is complete. After typing a plus sign, or any other mathematical operator for that matter, you can also click on the cell that you want to refer to rather than having to type the reference. Formula shown below: ## Adding multiple non-adjacent cellsIf the cells that you are adding together are all in the same column or row, the easiest way to add them is by "summing" them, which I will show you in just a moment… but when the cells that you are adding are not adjacent, this is when adding multiple, individual cells becomes extremely useful. If your sheet has numbers entered in varying locations that you want to add together, you can do so by using a formula like one that is shown in the example below. Formula used in example: ## Using the ADD function to addUsing the ADD function to add is much less common and less useful than using the plus sign operator, but here is an example that shows how to use it in case you have a need for it. Formula shown below: ## How to sum in Google SheetsThe SUM function is an extremely useful formula that will allow you to sum entire rows, columns, or specified ranges.
Let's say that you have a column of numbers that you want to add, but you don't want to have to have a long formula that adds lots of individual cells. This is where you would use the SUM function.
- Use the cell at the top of the column to enter a formula like this, which sums all of the cells below it: =SUM(C1:C)
- Or use a cell that is below the range that you want to sum, and enter a formula that contains the range of cells that are above it. In other words, for example if your SUM formula is in cell C100, then to sum the numbers in column A that are above your formula, make sure you specify an ending row in the range that is less than 100, like this: =SUM(C1:C99)
Formula shown in example image below: Note: If the range in your sum formula contains the cell that your formula is entered in, it will cause a circular dependency error. ## How to subtract in Google SheetsNow let's go over the different ways to subtract in a spreadsheet. Again I will show you how to subtract by using numbers alone, and I will also show you how to subtract with cell references.
## Subtract numbersHere is an example that shows how to subtract by simply using numbers directly in the formula. Formula shown in example below: After entering the formula above into a cell in your spreadsheet, and the cell will display an answer of "400". ## Subtract cells with numbersYou can also use cell references to subtract… where the numbers that you want to subtract from one another are entered in cells, and where you then refer to those cells within the formula to perform your calculation. Using cell references to subtract will allow you to change the numbers that your formula refers to very quickly and easily, without having to modify your formula.
- Click on cell A1, then type the number "3000", and then press enter
- Click on cell A2, then type the number "1000", and then press enter
- Click on cell A3, then type "=A1-A2", and then press enter
- Cell A3 should now display an answer of "2000"
Example formula: ## Using the MINUS function to subtractHere is an example that shows how to subtract by using the MINUS function. Formula shown below: ## How to multiply in Google SheetsNow let's go over how to multiply in a spreadsheet. Just like with addition and subtraction, you can choose whether you want to use ordinary numbers in your formula, or whether you want to use cell references… or a mix of the two.
## Multiply numbersIf you want, to multiply you can use your spreadsheet like a calculator, and simply type a formula like the one shown below, which does not use any cell references and had its numbers typed directly into the formula bar. Formula shown in example: Enter the formula above into a cell in your sheet, and the cell will display an answer of "70". ## Multiply cells with numbersAgain just like with addition and subtraction, you can use cell references in your multiplication formulas, where the numbers that you want to multiply are entered into individual cells, and where your formula refers to those cells to designate the values to be multiplied.
- Click on cell A1, then type the number "7", and then press enter
- Click on cell B1, then type the number "7", and then press enter
- Click on cell C1, then type "=A1*B1", and then press enter
- Cell C1 should now display an answer of "49"
Formula shown below: ## Using the MULTIPLY function to multiplyThe example below shows how to multiply by using the MULTIPLY function. However it is much more common and easy to use the operator method shown above. Formula shown below: ## How to divide in Google SheetsNow I'll show you how to divide in your spreadsheet! Once again, I'll show you the difference between using ordinary numbers vs. using cell references to divide… and I'll also show you the difference between using an operator vs. a function to divide.
Note that just like when using a calculator, you cannot successfully divide by 0 in a spreadsheet. If your denominator (number "on bottom" or after the slash) is 0, your division formula will display a #DIV/0 error. This error can be handled by using the IFERROR function which will allow you to specify what value should be displayed if the formula has an error. Here are two examples of handling the #DIV/0 error: - =IFERROR(C1/C2,0)
- =IFERROR(C1/C2,"Error")
## Divide numbersHere is an example that shows how to divide by using numbers only, without referring to any cells in the sheet. Formula shown below: Enter the formula above into any cell in your sheet, and the cell should display an answer of "20". ## Divide cells with numbersIf you want, you can enter the numbers that you want to divide into individual spreadsheet cells, and then refer to those cells in your division formula. This will be especially useful if you plan to change one or more of the values in your formula over time.
- Click on cell A1, then type the number "99", and then press enter
- Click on cell B1, then type the number "11", and then press enter
- Click on cell C1, then type "=A1/B1", and then press enter
- Cell C1 should now display an answer of "9"
Formula shown below: ## Using the DIVIDE function to divideThis example will show you how to divide in a Google spreadsheet by using the DIVIDE function. Formula shown below: This content was originally created and written by SpreadsheetClass.com ## How to square numbers in Google SheetsSquaring numbers in a spreadsheet is just as easy as the four basic mathematical operations that we have went over so far. In a spreadsheet, a carrot "^", symbolizes "To the power of", meaning that this is how we express an exponent in a spreadsheet… by combining a carrot with a number. So "^2" means "To the second power", or simply "squared". You can also choose any other number than 2 to raise a number to a specified power/exponent, such as cubing: "^3"
## Square numbersThe simplest way to square a number i your spreadsheet, is to simply type a formula like the one below. Formula shown in the example image below: Enter the formula above into any cell in your spreadsheet, and the cell should show an answer of "4". ## Square cells with numbersJust like with the other math operations, you can type the number that you want to square into a cell, and the (in another cell) refer to the cell with the number inside it with/in your square formula, as shown below.
- Click on cell A1, then type the number "2", and then press enter
- Click on cell B1, then type =A1^2, and press enter
- Cell B1 should now display an answer of "4"
Formula used in the example below: ## Square using the POWER functionThere is also a function that can be used to square, and it is called POWER. This function can also be written as simply POW. Here is an example of how to use this function. Formula shown below: Formula alternative: ## How to square root numbers in Google SheetsIf you need to square root a number in your spreadsheet, you can use the SQRT function.
## Square root numbersThe most simple way to square root a number in your sheet, is to simply enter an ordinary number directly into the SQRT formula, as shown below. Formula used in example below: Enter the formula shown above into a cell in your spreadsheet, and then the cell should display an answer of "3". ## Square root cells with numbersIf you want to use a cell reference to designate the value that you want to square root, so that when you change the number in the cell it also changes your answer… you can do this with the SQRT function as well by simply typing a cell address/reference in the function.
- Click on cell A1, then type the number "9", and then press enter
- Click on cell B1, then type =SQRT(A1), and press enter
- Cell B1 should now display an answer of "3"
Formula used in example below: ## How to calculate equations in Google SheetsGoogle Sheets can do much more than just simple math problems with two values. You can also set up math equations so that your sheet will automatically solve the equation as you enter/change the variables. Shown below is how to calculate the circumference and area in your spreadsheet, given a specified radius. Notice that these equations use numbers and cell references in the formula. The numbers entered directly into the formula, such as 3.14 (pi), which is a constant… and the cell references represent the radius, which is a variable and we want to be able to easily modify. Circle
Circle
Alternative formula: In the image above you can see that the circumference formulas are entered in column B, and that the area formulas are entered in column C. There is a formula in each blue cell in the image… and each formula refers to the radius that is listed adjacent to it. This was done by copying and pasting the formulas at the top (row 2), into the cells below.
Below you can see what the formulas will look like as they are copied into the rows below row 2 in the example above. |