1. Calculations with cell references
We've seen how cell references can be used to point to and return the values in other cells, but cell references really shine when they're used to perform calculations.
2. Recap: arithmetic in Google Sheets
Recall that Google Sheets formulas support common arithmetic operations, such as addition, subtraction, multiplication, division, and exponentiation, and that parentheses can also be used to specify the order of operations.
3. Recap: arithmetic in Google Sheets
So far, we performed calculations on cell values by looking up the value by-eye, and manually typing it into a formula, adding any operations we desire. Here, we're performing a percentage calculation using the base payment and interest rate values. Recall that we can take the percentage of a value by multiplying the value by the percentage in a formula.
4. Recap: arithmetic in Google Sheets
This is a slow process that won't scale well for larger datasets. It's also error-prone, as typos could easily be made when copying values by-hand, and what if we changed the values of, say, the interest rate? We'd have to manually edit each formula with the new values.
5. Performing arithmetic on cell references
Fortunately, we can use cell references in formulas to perform calculations, and overcome many of these issues.
Let's return to the example we just saw, where we calculated the interest by multiplying the base payment by the interest rate.
6. Performing arithmetic on cell references
We start with 900 as before, as the base payment is the same across banks, but instead of multiplying by 0.8%,
7. Performing arithmetic on cell references
we multiply by D2. The formula will now look up the value in D2, and multiply it by 900.
8. Performing arithmetic on cell references
We get the same result as before, but as we've seen, one of the main benefits of using cell references is that we can quickly copy references into neighboring cells.
9. Performing arithmetic on cell references
If we drag the bottom-right corner of F2 down to F5, we populate the rest of the cells with the correct results. Recall that when copying cells, the references in the formula will shift by the same amount, so when F2 is copied into F3,
10. Performing arithmetic on cell references
the cell reference to D2
11. Performing arithmetic on cell references
changes to D3.
12. Performing arithmetic on cell references
Likewise, the reference changes to D4
13. Performing arithmetic on cell references
and D5 when copied into cells F4 and F5, respectively.
14. Combining references in calculations
This is great, and way more efficient than the method we had before, but what if the base payments changed, and varied between banks?
15. Combining references in calculations
Our interest cells didn't update, as we used a fixed value for the base payment in the formula. The good news is that we aren't limited to a single cell reference in formulas - we can use as many as we like!
So instead of 900 in F2's formula,
16. Combining references in calculations
we can use the cell reference, E2, to point to the base payment for that bank.
17. Combining references in calculations
Then, as before,
18. Combining references in calculations
we can drag F2 down to copy the references.
19. Combining references in calculations
Now the values in the interest column will automatically update to any changes to the interest rate or base payment values. Pretty neat!
20. Comparing references
Spreadsheets formulas also support the comparison operations, we've seen previously. Comparison operations return true or false values depending on whether the criteria was met.
21. Comparing references
Let's use comparison operators to identify where the interest is more than eight dollars.
22. Comparing references
We start with the same formula for calculating the interest, multiplying the cell references for the interest rate and base payment.
23. Comparing references
Then, to check if this calculated value is greater than eight, we use the greater than operator, followed by eight.
24. Comparing references
For F2, this returns the value, FALSE, as the calculated interest is $7.20.
25. Comparing references
Copying the cell downwards lets us perform the same operation on each row, and quickly identify more expensive options. Like with the interest calculation, the logical values will update automatically to changes to the interest rate or base payment.
26. Let's practice!
Now it's your turn to perform cell reference calculations.