Excel offers several functions to compute average, and there are many ways how you can calculate it. Let’s dive right into the different methods for average computation in Excel.
Table of Contents
1. AVERAGE Function
The most common form of Average is the arithmetic mean. To your good, the same can be computed in Excel using the basic AVERAGE function. It involves summing all the values within a range and dividing the sum thereof by the number of values.
Syntax:
The syntax of the AVERAGE function reads as follows.
= AVERAGE (Number 1, Number 2, …. )
Where ‘Number 1’, ‘Number 2’, and so on represent the values to be averaged. These numbers can be values fed into the formula. Or you can replace them with cell references.
Note: An AVERAGE function can work out up to 255 arguments.
The AVERAGE function can be accessed from the Functions Library as follows.
Formulas > Functions Library > More Functions > Statistical Functions > Average
Examples:
Let’s look into an example to learn more about the operation of the AVERAGE function.
Beginning with a simple example – Let’s see how you may compute the average for a group of numbers in Excel.
For instance, to compute the average of numbers from 30 to 35 in Excel, populate them in the AVERAGE formula as follows.
= AVERAGE (30, 31, 32, 33, 34, 35)
Hit ‘Enter’ to see Excel compute the average for the same as follows.
Moving forward, let’s see how you may compute the average for a range of numbers populated in Excel.
The dataset below represents the marks of a student in different subjects.
To find the average grades scored by him in all the subjects, compose the Average formula as follows.
= AVERAGE (B2:B10)
Here, B2:B10 represents the range containing the values to be averaged in Excel.
Hit ‘Enter’ to yield results as follows.
Points to be Noted:
i. Text Values
In the above example, let’s manipulate the data to include a text value in it as follows.
Now, if we apply the AVERAGE function to it, here is how the results would change.
What caused the AVERAGE Value to change? When a dataset contains a text value, Excel doesn’t consider it for the computation of average.
So the calculation performed by Excel includes values populated in cells B2:B9 only. Furthermore, the sum of these values is divided by 8 and not 9.
Excel entirely omits Cell B10 from the calculation of average. Neither is it added up for summing the values nor is it considered in the number of values to be used for division.
Pro Tip: Excel applies the same treatment to any empty cell within the range. It is ignored for the average computation.
ii. Zero Values
Now, let’s manipulate the data in the above example to include a ‘zero’ value in it as follows.
If we apply the AVERAGE function to it, here is how the results would change.
Must be wondering what caused the AVERAGE Value to change this time? When a dataset contains a zero, Excel considers it for the computation of average as any normal value.
So the calculation performed by Excel includes all values populated in cells B2:B10. Furthermore, the sum of these values is divided by 9.
As zero is included in the computation of average, the average value sees a downturn.
2. AVERAGEA Function
The AVERAGA function is a tool to combat the issue with text values, as explained above. If your dataset contains text values that you want to be included in the Average computation, you may want to use the AVERAGEA function.
Let’s compare it through the example below.
Apply the AVERAGEA function to it as follows,
= AVERAGEA (B2:B10)
Hit ‘Enter’ to yield results as follows.
The average this time is the same as that computed with a ‘zero’ value in the range. This is because, under the AVERAGEA function, text values are not ignored. Instead, Excel assumes a text value to be equal to zero.
The AVERAGEA function can be accessed from the Functions Library as follows.
Formulas > Functions Library > More Functions > Statistical Functions > AVERAGEA
3. AVERAGEIF Function
The AVERAGEIF function enables Excel users to find the Average for only those values that meet a specific criterion.
Syntax:
=AVERAGEIF (range, criteria, average_range)
- Range: The cell range where the criteria are to be looked up for.
- Criteria: The criteria to be defined for the average of values.
- Average_Range: The cell range where the values to be averaged are populated.
Example:
The dataset below contains the marks of a student in different subjects along with their passing status.
If you want to average the marks of only those subjects that have been passed, compose the AVERAGEIF formula as follows.
= AVERAGEIF (C2:C10, “Passed”, B2:B10)
- C2:C10 defines the range to be looked up for the passing status.
- “Passed” defines the criteria for averaging. Excel will only average the values against which the status appears to be “Passed”.
- B2:B10 defines the range containing the values to be averaged.
Pro Tip: The criterion must be enclosed in double quotation marks.
Hit ‘Enter’ to yield results as follows.
Excel has only averaged the marks of those subjects where the corresponding status comes off to be ‘Passed’.
The AVERAGEA function can be accessed from the Functions Library as follows.
Formulas > Functions Library > More Functions > Statistical Functions > AVERAGEIF
Bottom Line:
Averaging in Excel is an easy job if you know the hidden ‘ifs and hows’ to different averaging functions of Excel. Practice the tips above to master the average functions of Excel.