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
- Start with
=, then the name and the arguments in parentheses: =NAME(arg1, arg2, …).
- English Excel separates arguments with
,; many European versions use ;. The trainer accepts both, and function names in English, Russian or Spanish.
- An argument can be a number, a reference (
A1), a range (A1:A10), text in quotes ("Paid"), a logical value or another function: =ROUND(AVERAGE(E2:E7), 0).
- Optional arguments can be left out, e.g. the last argument of
VLOOKUP.
Inserting a function
Start typing =SU… | Excel suggests names; Tab accepts a suggestion |
| Shift+F3 | Insert Function dialog: search by description and argument help |
| Alt+= | AutoSum of the selected range |
| Ctrl+A after the name | arguments dialog for the function you typed |
Function categories
| Math | SUM, ROUND, SUMPRODUCT, MOD, INT |
| Statistical | AVERAGE, MAX, MIN, COUNT, COUNTA, LARGE |
| Conditional | COUNTIF, SUMIF, SUMIFS, AVERAGEIF |
| Logical | IF, IFS, AND, OR, IFERROR |
| Lookup & reference | VLOOKUP, XLOOKUP, INDEX, MATCH |
| Text | TRIM, LEFT, SUBSTITUTE, SEARCH, TEXTJOIN |
| Date & time | DATE, YEAR, MONTH, DATEDIF, NETWORKDAYS, TODAY |
| Financial | PMT — 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/A | the 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
| Math | 1 SUM · 2 AVERAGE · 3 MAX · 4 MIN · 5 ROUND |
| Counting & conditions | 6 COUNTA · 7 COUNT · 8 COUNTIF · 9 SUMIF · 10 SUMIFS |
| Logic | 11 IF · 12 IFS · 13 AND · 14 OR · 15 IFERROR |
| Lookup | 16 VLOOKUP · 17 XLOOKUP · 18 MATCH · 19 INDEX · 20 LARGE |
| Text | 21 TRIM · 22 SUBSTITUTE · 23 LEFT · 24 SEARCH + ISNUMBER · 25 TEXTJOIN |
| Dates | 26 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 / MIN | largest / smallest value |
ROUND(number, digits) | 2 — to hundredths, 0 — to whole numbers, -2 — to hundreds: ROUND(1784, -2) = 1800 |
Tasks in the trainer
- In E2:E7 calculate each manager's sales for the quarter.
- In F2:F7 — average monthly sales.
- In I1 find the best quarterly total.
- In I2 — the worst total.
- In I3 calculate the average total and round it to hundreds.
Solve the tasks in the trainer →
Check yourself
- What does =ROUND(3.456, 1) return?
- A formula shows #NAME?. What most likely happened?
- What separates arguments in English Excel?