Core functions: SUM, AVERAGE, MIN, MAX, COUNT

In this lesson you will learn

Explain the structure of a function (name, brackets, arguments)
Use SUM, AVERAGE, MIN, MAX, COUNT, COUNTA and ROUND
Calculate quickly with AutoSum (Alt+=)

Teacher Nigar has 25 students in her class. To find their average score you could write =(B2+B3+B4+…+B26)/25, but that formula is long and easy to get wrong. Instead, =AVERAGE(B2:B26) is all you need. Excel has hundreds of ready-made functions like this; in this lesson you will learn the most popular ones.

How is a function built?

Definition

Function

A ready-made Excel formula. Syntax: =NAME(arguments). Arguments are the values the function works with — numbers, cells or ranges — separated by commas.

Excel

=SUM(B2:B10)
=SUM(B2:B10,D2:D10,5)
=ROUND(AVERAGE(B2:B10),1)

The sum of one range; the sum of two ranges and a number; a function inside another — the average rounded to one decimal place.

Function	What it does	Example
SUM	adds numbers	`=SUM(B2:B7)`
AVERAGE	finds the average	`=AVERAGE(B2:B7)`
MIN	returns the smallest value	`=MIN(B2:B7)`
MAX	returns the largest value	`=MAX(B2:B7)`
COUNT	counts cells that contain numbers	`=COUNT(B2:B7)`
COUNTA	counts all non-empty cells	`=COUNTA(A2:A7)`
ROUND	rounds a number to a given number of decimals	`=ROUND(B10,1)`

AutoSum and Insert Function

1
Select the result cell
Click the cell right below the column of numbers, for example B8.
2
Run AutoSum
Click Home › Editing › AutoSum (Σ) or press Alt+=. Excel suggests the range above and shows it with a moving border.
3
Check and confirm
If the range is right, press Enter; if not, select the correct cells with the mouse. The arrow next to Σ offers Average, Count Numbers, Max and Min.
4
Use fx for other functions
The fx button next to the formula bar (Shift+F3) opens Insert Function: search for a function, pick it and fill in each argument with on-screen help. When you start typing =AV, Excel also lists matching functions — choose one with Tab.

Interactive

Loading simulation…

A class's test scores and six core functions.

Empty cells, text and zeros

COUNT counts only numbers (dates are numbers too); COUNTA counts every non-empty cell, including text.
AVERAGE, SUM, MIN and MAX ignore empty cells and text in a range.
A zero, however, is a normal number and pulls the average down. Typing 0 for an absent student and leaving the cell empty give different results.

AutoSum: insert a SUM formula automaticallyAlt+=

Open the Insert Function dialogShift+F3

Accept the suggested function nameTab

Key points

A function is written =NAME(arguments), with arguments separated by commas.
SUM — total, AVERAGE — mean, MIN/MAX — smallest and largest, ROUND — rounding.
COUNT counts only numbers; COUNTA counts every non-empty cell.
Empty cells and text are left out of an average; zeros are included.
Alt+= runs AutoSum; localized Excel may use different names and a ; separator.

Check yourself

10 questions. Every correct answer earns XP.

1 / 10

Which function finds the largest value in a range?