Formulas and functions

Writing formulas, the order of operations, error values, relative and absolute references, and the core functions with conditional totals.

About 3 hours of study

Learning objectives

Lessons

  1. 1

    Formulas and operators

    How to write formulas that calculate from other cells, the arithmetic and comparison operators, the order Excel works them out in, and what the common error values mean.

  2. 2

    Relative, absolute and mixed references

    What happens to cell references when a formula is copied, how dollar signs lock a column or row, how F4 switches between the kinds, and how named ranges make formulas readable.

  3. 3

    SUM, AVERAGE, COUNT and conditional totals

    Excel's most used functions: SUM, AVERAGE, MIN, MAX, COUNT, COUNTA and COUNTBLANK, ROUND, and the conditional versions SUMIF, COUNTIF, AVERAGEIF, SUMIFS and COUNTIFS.

Module practice

Module quiz

1. What does =10-2*3 return?
2. Which error appears when dividing by an empty cell?
3. Which reference stays fixed when copied anywhere?
4. Which function counts cells that hold numbers?
5. In SUMIFS, which argument comes first?
6. Why does copying =B2*F1 down a column give zeros below the first row?

‹ Getting startedLogic, text and dates ›