Course contents · Excel functions

Functions overview. Math

Open the practice in the trainer →

A function is a ready-made formula with a name. You pass it arguments and it returns a result: =SUM(B2:D2).

Syntax

Inserting a function

Start typing =SU…Excel suggests names; Tab accepts a suggestion
Shift+F3Insert Function dialog: search by description and argument help
Alt+=AutoSum of the selected range
Ctrl+A after the namearguments dialog for the function you typed

Function categories

MathSUM, ROUND, SUMPRODUCT, MOD, INT
StatisticalAVERAGE, MAX, MIN, COUNT, COUNTA, LARGE
ConditionalCOUNTIF, SUMIF, SUMIFS, AVERAGEIF
LogicalIF, IFS, AND, OR, IFERROR
Lookup & referenceVLOOKUP, XLOOKUP, INDEX, MATCH
TextTRIM, LEFT, SUBSTITUTE, SEARCH, TEXTJOIN
Date & timeDATE, YEAR, MONTH, DATEDIF, NETWORKDAYS, TODAY
FinancialPMT — loan payment
Dynamic arrays (Excel 365)UNIQUE, FILTER, SORT — the result "spills" into several cells; not available in the trainer

When you get an error instead of a result

#DIV/0!division by zero or by an empty cell
#N/Athe value was not found (VLOOKUP, MATCH)
#VALUE!wrong argument type: text instead of a number
#NAME?misspelled function name or text without quotes
#REF!reference to a deleted or non-existent cell

The 30 key functions of this module

Math1 SUM · 2 AVERAGE · 3 MAX · 4 MIN · 5 ROUND
Counting & conditions6 COUNTA · 7 COUNT · 8 COUNTIF · 9 SUMIF · 10 SUMIFS
Logic11 IF · 12 IFS · 13 AND · 14 OR · 15 IFERROR
Lookup16 VLOOKUP · 17 XLOOKUP · 18 MATCH · 19 INDEX · 20 LARGE
Text21 TRIM · 22 SUBSTITUTE · 23 LEFT · 24 SEARCH + ISNUMBER · 25 TEXTJOIN
Dates26 YEAR · 27 MONTH · 28 DATEDIF · 29 NETWORKDAYS · 30 DATE

Math: functions 1–5

SUM(range)sum of numbers; text and empty cells are skipped
AVERAGE(range)arithmetic mean
MAX / MINlargest / smallest value
ROUND(number, digits)2 — to hundredths, 0 — to whole numbers, -2 — to hundreds: ROUND(1784, -2) = 1800

Tasks in the trainer

  1. In E2:E7 calculate each manager's sales for the quarter.
  2. In F2:F7 — average monthly sales.
  3. In I1 find the best quarterly total.
  4. In I2 — the worst total.
  5. In I3 calculate the average total and round it to hundreds.

Solve the tasks in the trainer →

Check yourself

  1. What does =ROUND(3.456, 1) return?
  2. A formula shows #NAME?. What most likely happened?
  3. What separates arguments in English Excel?