Get Certified for Free Browse courses

Spreadsheets · Level 2 of 5 · Foundations

Functions and references

AVERAGE, COUNT, IF, and relative versus absolute references.

Study notes

  1. AVERAGE, MIN, MAX, and COUNT summarize a range. COUNT counts numbers; COUNTA counts non-empty cells.
  2. IF returns one value when a condition is true and another when false: =IF(B2>=70,"Pass","Retake").
  3. A relative reference like A1 changes when you copy a formula. An absolute reference like $A$1 stays fixed.
  4. Use absolute references for constants such as a tax rate stored in one cell.
  5. COUNTIF and SUMIF count or add only cells that meet a condition: =SUMIF(A:A,"West",C:C).

Quiz: 6 questions

Choose one answer for each question, then check your answers. Score 5 of 6 to pass. Then enter your name and email to download a PDF certificate and have a copy emailed to you. Answers are graded in your browser.

1. =AVERAGE(2,4,6) returns:

2. =IF(B2>=70,"Pass","Retake") with B2 = 65 returns:

3. You copy =A1*$B$1 down one row. It becomes:

4. Which counts non-empty cells including text?

5. =SUMIF(A:A,"West",C:C) adds:

6. Why store a tax rate in one cell and reference it with $?

About this certificate: Certificates are issued by Get Certified for Free for passing a free online knowledge quiz. They are not accredited, licensed, government, academic, or professional credentials and do not confer a license. Read the full terms.