Excel - Average a group of numbers Tutorial

In this video, you will learn how to average a group of numbers using Microsoft 365. The video demonstrates how to use the AutoSum feature to quickly calculate averages in Excel.
By clicking the cell to the right of a row or below a column, you can access the AutoSum dropdown menu and select the Average option.
The video also explains how to verify the formula and save time when averaging a few cells.
Additionally, it covers how to average values and cells that are not adjacent by using the Average function and ignoring empty cells or those containing text.
This tutorial will help you efficiently calculate averages in Microsoft 365.

2:01
3626 views

Available in Microsoft365

Objectifs :

Understand how to use the AutoSum feature in Excel to calculate averages and learn about the Average function, including how to handle non-adjacent cells and the AverageIf function.

Chapitres :

Introduction to AutoSum in Excel
The AutoSum feature in Excel is commonly used to quickly add numbers. However, it can also be utilized to calculate other results, such as averages. This section will guide you through the process of using AutoSum for averaging values.
Calculating Averages with AutoSum
To calculate an average using AutoSum, follow these steps: 1. Click the cell to the right of a row or below a column where you want the average to appear. 2. On the Home tab, click the AutoSum down arrow. 3. Select 'Average' from the dropdown menu. 4. Verify that the formula displayed is correct and press Enter. After pressing Enter, you will see a formula in the cell, such as 'AVERAGE(A2:A5)', which indicates that it averages the values from cells A2 through A5.
Understanding Limitations of AutoSum
When using AutoSum to average a few cells, it can save time. However, it has limitations. For instance, if you try to use the AutoSum average option on a column where one cell is blank (e.g., C4), it will only consider the non-blank cell (C5) for the average. If C4 contained a number, AutoSum would recognize the range from C2 through C5 as adjacent.
Averaging Non-Adjacent Cells
To average values from non-adjacent cells or ranges, you can manually enter the formula. Here’s how: - Type an equal sign followed by 'AVERAGE(' - Hold down the Ctrl key and click on the desired cells and ranges. - Close the parenthesis and press Enter. This method allows the Average function to ignore empty cells or those containing text, providing a more accurate average.
Introduction to AverageIf Function
In the next section, we will explore the AverageIf function, which allows you to calculate averages based on specific criteria. This function is particularly useful for analyzing data sets where you need to consider only certain values.

FAQ :

How do I use AutoSum to calculate an average in Excel?

To use AutoSum for calculating an average, click the cell next to the row or below the column you want to average. Then, on the Home tab, click the AutoSum down arrow and select 'Average'. Verify the formula and press Enter.

What happens if there are blank cells in my range when using the Average function?

If there are blank cells in your range, the Average function will ignore them when calculating the average, which can lead to a more accurate result.

Can I average non-adjacent cells in Excel?

Yes, to average non-adjacent cells, type an equal sign followed by 'AVERAGE(', hold down the Ctrl key, select the desired cells, and then press Enter.

Why does AutoSum only use certain cells for averaging?

AutoSum only recognizes adjacent ranges. If there is a blank cell in the range, it may not include all desired cells in the calculation.

What is the syntax for the Average function in Excel?

The syntax for the Average function is AVERAGE(number1, [number2], ...), where you can input the range of cells you want to average.

Quelques cas d'usages :

Calculating Monthly Sales Averages

A sales manager can use the Average function to calculate the average sales for each month by selecting the range of sales data for the months and applying the Average function to assess performance.

Analyzing Student Grades

A teacher can average student grades by selecting the cells containing the grades. Using AutoSum to calculate the average helps in quickly determining the overall performance of the class.

Budgeting and Expense Tracking

A financial analyst can use the Average function to analyze monthly expenses by averaging the costs recorded in a spreadsheet, helping to identify spending trends and areas for cost reduction.

Project Time Estimation

A project manager can average the time taken for similar tasks across different projects to estimate future project timelines more accurately, using the Average function to process the data.

Performance Metrics in HR

An HR professional can average employee performance scores from various evaluations to gauge overall employee performance, using the Average function to streamline the analysis.

Glossaire :

AutoSum

A feature in Excel that allows users to quickly calculate the sum of a range of cells. It can also be used for other functions like average, count, etc.

Average Function

A mathematical function in Excel that calculates the mean of a set of numbers. The syntax is AVERAGE(number1, [number2], ...).

Adjacent Range

A set of cells that are next to each other in a row or column. For example, A2 to A5 is an adjacent range.

Non-Adjacent Cells

Cells that are not next to each other. To average non-adjacent cells, you can hold down the Ctrl key while selecting the desired cells.

Formula

An expression in Excel that performs calculations on values in cells. Formulas begin with an equal sign (=).

Empty Cells

Cells that do not contain any data or values. They are ignored in calculations like averages.

00:00:05

But did you know you, can also use it to calculate

00:00:09

other results, such as averages.

00:00:15

Click the cell to the right of a row, or below a column.

00:00:20

Then on the Home tab, click the AutoSum down arrow,

00:00:24

click Average, verify the formula is what you want and press Enter.

00:00:31

When I double-click inside the cell, I see it's a formula

00:00:34

with the average function.

00:00:37

The formula is: average A2:A5,

00:00:46

which averages the cells from A2 through A5.

00:00:53

When averaging a few cells, the average function saves you time.

00:00:57

With larger ranges of cells, it's essential.

00:01:02

If I try to use the AutoSum average option here,

00:01:06

it only uses Cell C5, not the entire column.

00:01:11

Why? Because C4 is blank.

00:01:14

If C4 contained a number, C2 through C5 would be

00:01:19

an adjacent range of cells, that AutoSum would recognise.

00:01:25

To average values and cells and ranges of cells that aren't adjacent,

00:01:29

type an equal sign, a formula always starts with an equal sign,

00:01:35

Average,

00:01:38

open parenthesis,

00:01:40

hold down the Ctrl key,

00:01:42

click the desired cells and ranges of cells,

00:01:47

and press Enter.

00:01:50

The formula uses the Average function to average

00:01:52

the cells containing numbers and ignores the empty cells

00:01:56

or those containing text.

00:01:58

Up next, Averageif.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2796 Views

Video Training Microsoft Excel - Discovery level

Discover and familiarize yourself with Excel: from creating and using your tables to enhancing your visuals and collaboration. Our introduct...

ALL COURSES