Once you have a column of figures, you often want to know the typical value, the smallest, or the largest, without checking every row by eye. By the end of this page you will be able to use AVERAGE, MIN and MAX to answer those questions in one step.
You have already learned that AutoSum can quickly total a column of numbers. AVERAGE, MIN and MAX work in a very similar way, but instead of adding everything up, they answer a specific question about a group of cells: what is the typical figure, what is the lowest, and what is the highest.
- AVERAGE of the week
- MIN, the coldest day
- MAX, the warmest day
The pattern for writing any of them is the same: an equals sign, the function's name, then the range of cells in round brackets. For example, if daily temperatures are in cells B2 to B8, =AVERAGE(B2:B8) gives you the average temperature across that week, =MIN(B2:B8) gives you the coldest day, and =MAX(B2:B8) gives you the warmest. The colon between B2 and B8 means 'through to', so B2:B8 means every cell from B2 down to B8 inclusive.
Try it yourself
In cells B2 to B8, type seven different numbers, such as a week of temperatures.
This gives you a real range of figures to summarise.
In B10, type =AVERAGE(B2:B8) and press Enter.
This calculates the mean of all seven numbers in one step.
In B11, type =MIN(B2:B8) and press Enter.
This finds the smallest number in the range without you having to scan for it.
In B12, type =MAX(B2:B8) and press Enter.
This finds the largest number in the range in the same way.
Change one of the numbers in B2 to B8 and watch all three results update.
This confirms the functions are reading live from the cells, not from numbers frozen in place.
Words people use
These three functions are some of the most used in all of Excel, precisely because so many real questions boil down to typical, lowest and highest. They also combine well with AutoSum and with formatting, which you will meet in other lessons.
Check yourself
If you can say yes to each of these, you are ready for the next lesson.
- I can write an AVERAGE formula for a range of cells.
- I can write a MIN formula to find the smallest value.
- I can write a MAX formula to find the largest value.
- I understand how a colon defines a range such as B2:B8.