Let’s say you’re a team manager tracking the completion rates of your team members’ tasks for the week. You have a list of percentage values showing how many tasks each person completed, such as 80%, 90%, and 75%.
(80% + 90% + 75%) / 3 = 81.67%
This average percentage gives you an idea of the overall completion rate across all tasks.
Now, to average percentage values in Excel, you can use the AVERAGE function. Refer to the range where you have the values and then hit enter to get the result. All you need to do is that the cell where you are using this formula needs to have the percentage cell format.
Key Points
- The simplest way to calculate an average is to use =AVERAGE(range), where “range” is your cells with percentage values.
- If there are error values in the range, use =AGGREGATE(1, 6, range) to calculate the average while ignoring errors.
- You can use =AVERAGEIF(range, “>0”) to calculate the average of cells that meet specific criteria, like being greater than zero.
- And use =SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4) to calculate a weighted average.
In this tutorial, we will learn to use all of these methods in details and in the end, I have also share a custom function code that you can use to get the average of the percentage values. And this function, take care of the requirements one can have.
Formula to Average Percentages in Excel
AVERAGE function finds the middle value of a group of values (percentages). It adds up all the numbers and divides by the count of numbers. To use it, type =AVERAGE(range) where “range” is the group of cells you want to average.
For example, =AVERAGE(A1:A3) calculates the average of the values in cells A1 to A3.
=AVERAGE(A1:A3)
You can use the below steps for this:
- First, in a cell type =AVERAGE.
- Now, enter starting parentheses “(“.
- After that, refer to the range where you have the values that you want to calculate the average of.
- In the end, enter the closing parentheses and hit enter to get the result.
The moment you hit enter it returns the average of percentage values from the range. Excel is smart enough to change the cell format to percentage when you hit enter.
Make sure that the percentage values are correctly formatted as percentages in Excel to avoid errors in calculation.
Dealing with Errors and Text Values
Now, there might be a situation where you have a non-percentage value in the range. In this case, we need to write a formula which can deal with this.
So to calculate the average when some cells have text values or errors, you can use the AVERAGEIF or AGGREGATE. Here’s how to do it:
=AVERAGEIF(A1:A3, ">0")
The above formula calculates the average of the numbers in cells A1 to A3, but only includes values greater than 0. It ignores any text or error values in those cells.
=AGGREGATE(1, 6, A1:A3)
The above formula calculates the average numbers in cells A1 to A3, ignoring errors. It ensures errors are not included in the calculation.
- 1: This is the number to tell the function to average the values.
- 6: This tells the function to ignore errors in the specified range.
- A1:A3: This is the range of cells that you want to average.
Weighted Average of Percentages
Yes, you can calculate the weighted average of percentages also. Below we have the number of students who scored above the percentage slab. And now you need to calculate the weighted average of these percentage values
=SUMPRODUCT(A2:A4,B2:B4)/SUM(A2:A4)
This formula is a combination of SUMPRODUCT and SUM.
- SUMPRODUCT(A2:A4, B2:B4) – SUMPRODUCT multiplies each count of students with the percentage and then sums up all those products.
- SUM(A2:A4) – SUM returns the total number of students (sum of the count of students).
- Division (/) – In the end, divide both values to get the weighted average of percentages.
Use AVERAGEIF to Average Percentages
AVERAGEIF calculates the average of cells that meet a specific condition. And here, you can also use AVERAGEIF for averaging percentages with a condition to calculate the average. Let’s say you want to average values that are above 50%.
You can use the below formula:
=AVERAGEIF(A2:A12,">50%")
It calculates the average of the values in cells A2 to A12, but only includes values that are greater than 50%. It ignores any values that are 50% or less.
Custom Function
If you calculate the average percentage a lot, I suggest using a custom function. And here’s a VBA code for a custom function that calculates the average of percentage values, ignores errors and text values, and can handle non-contiguous ranges:
Function AveragePercentage(ParamArray ranges() As Variant) As Double
Dim cell As Range
Dim total As Double
Dim count As Long
Dim i As Long
total = 0
count = 0
' Loop through each range provided as parameter
For i = LBound(ranges) To UBound(ranges)
' Loop through each cell in the current range
For Each cell In ranges(i)
' Check if the cell contains a numeric value and is not an error
If IsNumeric(cell.Value) And Not IsError(cell.Value) Then
total = total + cell.Value
count = count + 1
End If
Next cell
Next i
' Calculate the average if count is greater than 0
If count > 0 Then
AveragePercentage = total / count
Else
AveragePercentage = 0
End If
End Function To use this function in your Excel workbook, Press Alt + F11 to open the VBA editor, and then go to Insert > Module to create a new module. Paste the code into the module.
Use the AveragePercentage function in your Excel worksheet like below:
=AveragePercentage(A1:A10, C1:C10, E1:E10)