Calculations with cell references

Introduction to Google Sheets

James Chapman

Curriculum Manager, DataCamp

Recap: arithmetic in Google Sheets

  • Spreadsheets supports common arithmetic operations:
    • Addition: +
    • Subtraction: -
    • Multiplication: *
    • Division: /
    • Exponentiation: ^
    • Specify order of operations: ()
Introduction to Google Sheets

Recap: arithmetic in Google Sheets

Filling out percentage calculations by looking up the values by-eye, and manually inputting them.

Introduction to Google Sheets

Recap: arithmetic in Google Sheets

 

  • Slow process → won't scale for larger datasets
  • Error-prone due to typos when copying values
  • Each formula would have to be manually edited if values change
Introduction to Google Sheets

Performing arithmetic on cell references

The bank information spreadsheet with an empty interest column.

Introduction to Google Sheets

Performing arithmetic on cell references

A partially-filled formula for calculating the interest, where we start with the base payment then the multiplication operator.

Introduction to Google Sheets

Performing arithmetic on cell references

The completed formula for calculating the interest, where we start with the base payment then the multiplication operator, and finally a cell reference to the interest rate. The cell reference used in the formula is also highlighted in orange.

Introduction to Google Sheets

Performing arithmetic on cell references

The interest formula containing the cell reference returned the same value, 7.2, as the manual calculation.

Introduction to Google Sheets

Performing arithmetic on cell references

The interest column has been populated by copying the first cell downwards.

Introduction to Google Sheets

Performing arithmetic on cell references

The cell reference is D2 in the F2 formula.

Introduction to Google Sheets

Performing arithmetic on cell references

The cell reference is D3 in the F3 formula.

Introduction to Google Sheets

Performing arithmetic on cell references

The cell reference is D4 in the F4 formula.

Introduction to Google Sheets

Performing arithmetic on cell references

The cell reference is D5 in the F5 formula.

Introduction to Google Sheets

Combining references in calculations

The base payments are now different for each bank, and the interest column didn't update following these changes.

Introduction to Google Sheets

Combining references in calculations

The base payments are now different for each bank, and the interest column didn't update following these changes.

Introduction to Google Sheets

Combining references in calculations

The fixed value of 900 in the F2 formula has been replaced with a cell reference to E2.

Introduction to Google Sheets

Combining references in calculations

Replacing the fixed value with a cell reference has yielded the same result, apart from minor formatting changes.

Introduction to Google Sheets

Combining references in calculations

The F2 formula containing two cell references has been copied down to update the remaining cells.

Introduction to Google Sheets

Combining references in calculations

The interest values now update to changes to both the base payment and interest rate values.

Introduction to Google Sheets

Comparing references

  • Comparison operators:

    • Equal to: =
    • Not equal to: <>
    • Greater than: >
    • Less than: <
    • Greater than or equal to: >=
    • Less than or equal to: <=

  • Return TRUE or FALSE values

Introduction to Google Sheets

Comparing references

A column has been created to identify where the interest is greater than eight dollars.

Introduction to Google Sheets

Comparing references

The formula to identify rows where the interest is greater than eight dollars started with the same interest calculation as before, by multiplying the two cells references to the interest rate and base payments cells.

Introduction to Google Sheets

Comparing references

The comparison formula is completed using the greater than comparison operator, followed by eight.

Introduction to Google Sheets

Comparing references

The formula performing the comparison returns FALSE for the first row.

Introduction to Google Sheets

Comparing references

Copying the comparison formula into the remaining cells returns a TRUE or FALSE value for each row.

Introduction to Google Sheets

Let's practice!

Introduction to Google Sheets

Preparing Video For Download...