Download Practice Workbook
Download the Excel workbook.
Method 1 – Calculating the Simple Arithmetic Average of a Group of Numbers
1.1 Using the Excel AutoSum to Find Average for a Group of Numbers Quickly
This is the sample dataset.
Step 1:
- Select a cell to see the output: D17.
- Go to the Formulas tab.
- In AutoSum, select Average.
Step 2:
- Select the sales values in the Sales column.
Step 3:
- Press Enter.
You’ll see the average sales in D17.
1.2 Using the Basic AVERAGE Function to Calculate the Average in Excel
- Enter the formula in D17:
=AVERAGE(D5:D15)
1.3 Finding the Average Manually
Use the formula:
=SUM(D5:D15)/COUNTA(D5:D15)
1.4 Using the Excel SUBTOTAL Function to Find the Average
- Enter the formula in D17:
=SUBTOTAL(1,D5:D15)
Method 2 – Calculating the Weighted Average with the SUMPRODUCT and the SUM Functions
The dataset below showcases a student’s marks in a final exam. Each subject has a weightage percentage.
To calculate the average marks in D14.
- Use the following formula:
=SUMPRODUCT(C5:C12,D5:D12)
- Press Enter.
This is the output.
If the weightage percentages do not add up to 100%:
- Use the following formula:
=SUMPRODUCT(C5:C12,D5:D12)/SUM(D5:D12)
- Press Enter.
This is the output.
Method 3 – Finding the Average with a Single Criterion
To know the average sales of all branches in California:
- Select D18.
- Enter the formula:
=AVERAGEIF(B5:B15,D17,D5:D15)
- Press Enter to see the output.
Read More: How to Calculate Average of Multiple Ranges in Excel (3 Methods)
Method 4 – Calculating the Average Ignoring Zero (0) Values
- Select D17.
- Enter the formula:
=AVERAGEIF(D5:D15,"<>"&0)
- Press Enter to see the output.
Read More: How to Calculate Average in Excel Excluding 0 (2 Methods)
Method 5 – Calculating the Average with Multiple Criteria in Excel
To know the average sales in California and Texas:
- Enter the formula below:
=AVERAGEIFS(D5:D15,B5:B15,”California”,B5:B15,”Texas”)
- Press Enter.
The function returns a #DIV/0! error. (It cannot return the output from a single column).
- Use this formula:
=SUM(SUMIF(B5:B15,D17:D18,D5:D15))/ SUM(COUNTIF(B5:B15,D17:D18))
This is the output.
Formula Breakdown
- SUMIF(B5:B15,D17:D18,D5:D15): returns the total sales for California and Texas in an array. The output is: {118133;77690}
- SUM(SUMIF(B5:B15,D17:D18,D5:D15)): adds up the total sales and returns $1,95,823.00.
- COUNTIF(B5:B15,D17:D18): counts cells containing ‘California’ and ‘Texas’ and returns: {4;3}
- SUM(COUNTIF(B5:B15,D17:D18)): sums the total counts and returns 7.
- SUM(SUMIF(B5:B15,D17:D18,D5:D15))/ SUM(COUNTIF(B5:B15,D17:D18)): divides the total sales for California and Texas by the total counts and returns: $27,974.71.
Similar Readings
- How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)
- How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)
- Average Attendance Formula in Excel (5 Ways)
- How to Calculate Centered Moving Average in Excel (2 Examples)
Method 6 – Calculating the Average of Top or Bottom 3 with the LARGE or the SMALL Functions in Excel
- Select D17.
- Enter the formula:
=AVERAGE(LARGE(D5:D15,{1,2,3}))
- To determine the average of the bottom 3 sales, use the formula:
=AVERAGE(SMALL(D5:D15,{1,2,3}))
Method 7 –Ignoring the #DIV/0! Error While Calculating the Average in Excel
The #DIV/0! error is displayed when a numeric value is divided by zero (0).
- Select D17.
- Enter the formula:
=IFERROR(AVERAGE(E5:E15),"No Data Found")
Method 8 – Using the Excel AVERAGEA Function
There are 3 text values in the Sales column.
To convert the text values to ‘0’:
- Use the formula:
=AVERAGEA(D5:D15)
Note: The output must be less than or equal to the output of the AVERAGE function for a similar cell range of cells.
Method 9 – Calculating Other Types of Average in Excel: Median and Mode
9.1 Using the MEDIAN Function
In the dataset below, the average sales value is $26,277.55, but the median is $29,964.00.
- Use the formula:
=MEDIAN(D5:D15)
You can sort the Sales column in ascending or descending order: there are 11 sales values in the column. The median is in the middle or 6th row.
9.2 Using the MODE Function
In the table below, $21,000.00 is displayed thrice in the Sales column.
Use the formula:
=MODE(D5:D15)
9.3 Using the MODE.MULT Function
In the dataset below, the function returned $16,000.00 although there is a sales value of $21,000.00 with the same number of occurrences as $16,000.00.
- If you sort the Sales column in descending order, the MODE function will return $21,000.00.
- Use the following formula:
=MODE.MULT(D5:D15)This is the output.
9.4 Using the MODE.SNGL Function
- Use the formula:
=MODE.SNGL(D5:D15)
Method 10 – Calculate the Moving Average with the Excel Analysis ToolPak
Calculate the moving average with a specific interval:
Step 1:
- Go to the Data tab.
- Select Data Analysis in Analysis.
Step 2:
- Choose Moving Average and click OK.
Step 3:
- In Input Range, select the entire column of total sales with its header.
- Check Label in the First Row option.
- Define a cell as the Output Range.
- Check Chart Output.
- Click OK.
The default interval is 3. The moving average is counted every 3 successive sales values.
This is the output.
The following chart displays the data points of the total sales and the moving averages. If you click the chart, you will see the reference columns in the data table.
Method 11 – Applying the TRIMMEAN Function to Calculate the Trimmed Mean in Excel
Select the trim percentage as 20% or 0.2.
- Use the formula:
=TRIMMEAN(D5:D15,0.2)
- Press Enter.
You’ll see the average sales of a trimmed sales range.
The simple AVERAGE function returns the same value: $26,134.11.
Related Articles
- Running Average: How to Calculate Using Excel’s Average(…) Function
- How to Generate Moving Average in Excel Chart (4 Methods)
- Calculate Moving Average in Excel (4 Examples)
- How to Calculate Exponential Moving Average in Excel
- How to Calculate Percentage above Average in Excel (3 Easy Ways)
