1. Cell mathematics!
Great work so far!
2. Formulas
Up to this point, we've known the exact values to enter into our Google Sheets, but in many cases, these values will have to be calculated from other values.
Google Sheets formulas allow us to perform these calculations, and they support common arithmetic operations, like addition, subtraction, multiplication, and division, much like a calculator.
One of the beauties of formulas is that interested stakeholders can see how each cell value was calculated.
3. Writing formulas
To write a cell formula, select the cell to edit and type an equals sign. The equals tells the spreadsheet that everything following it will be part of a formula, rather than writing a specific value to the cell, as we've done previously. We can then add numbers and operations to build up the formula.
4. Arithmetic examples
The plus sign is used in addition, a dash for subtraction, an asterisk for multiplication, and a forward-slash for division. See that the formulas handled decimal numbers as well as integers. In the division example, eight over three evaluates to 2.6 recurring, meaning that the sixes go on forever, but notice that the spreadsheet rounded the displayed value.
One common error when dividing in formulas is the dividing by zero error, which is displayed as #DIV/0! This tells us that we inadvertently divided by zero in our formula, and the calculation can't be computed.
5. More advanced arithmetic
Google Sheet formulas also support more advanced arithmetic operations, like exponentiation. The caret acts as the exponentiation operator, and is used to place one number to the power of another. For example, two to the power of three is 8.
Parentheses can also be used to specify the order of operations. In this example, the parentheses tell the formula to perform the addition before the multiplication, so two add three is evaluated to five, and then multiplied by six, to return 30.
6. Order of operations
When writing a long and complex formula, it's important to know that the spreadsheet will execute the operations in a specific order.
First, expressions inside parentheses are executed, then the exponents, followed by multiplication and division, and finally, addition and subtraction.
7. Editing formulas
After writing a formula, we may decide to change it. Let's change the formula in cell D2. First, select the cell.
8. Editing formulas
Notice that the formula appears in a box at the top of the spreadsheet.
9. Editing formulas
This formula bar can be used to edit the formula used in the cell. Here, we've changed the formula to divide by 150 instead of 300.
10. Editing formulas
Again, after making any edits, we should press enter or return to store the new formula. Notice that D2 changed from 0.8% to 1.6.
11. Calculating percentages
A really common application of formulas is percentage calculations. Let's say we want to populate cell F2 with 0.8% of 900 dollars.
12. Calculating percentages
We use an equals sign to begin our formula,
13. Calculating percentages
type the value we want to find the percentage of, 900,
14. Calculating percentages
then multiply by 0.8%. Using the percent operator in the formula meant we avoided having to divide and multiply to calculate the percentage manually.
15. Calculating percentages
The formula returns the correct result of 7.2.
16. Let's practice!
Head on over to the exercises to begin writing your very own formulas!