How to Calculate the Average Percentage in Excel

Percentage Basics

A percentage is a quantity or ratio that is a fraction of 100. Divide the number by the whole number and multiply by 100 to calculate the percent of a number.

Average Basics

In a set of numbers, the average value is the value, calculated by dividing the total by the number of numbers. This is a basic Average Formula.

Average = (Sum of numbers of a set) / (Total numbers in that set)

Introduction to Average Percentages

The average percentage is the mean value of percentages.

Common Mistakes While Calculating Average Percentage

The AVERAGE formula is used to determine the average of quantities. You can’t calculate the average percentage using this formula:

How to Calculate Average Percentage in Excel – 3 Easy Methods

The following is an overview of the sample dataset.

Method 1 – Combining the SUMPRODUCT and SUM Functions to Calculate the Average Percentage

Steps:

Select D7 and enter the following SUMPRODUCT and SUM functions.

=SUMPRODUCT(C5:C6,D5:D6)/SUM(C5:C6)

Press Enter.

The average percentage (0.64) is returned by the SUMPRODUCT and SUM functions.

Select D7. In the Home tab, go to Home → Number → Percentage to convert the fraction into a percentage.

Average percentages will be calculated,

Method 2 – Calculating the Average Percentage Using Data from A Survey

Step 1: Determine the number of each percentage

Select E5, and enter this formula.

=C5*D5

Press Enter. The result is 320.

AutoFill the the rest of the cells in column E.

Note: You can also convert the percentage into a decimal:

Select D5:D6. Press Ctrl + 1.

In Format Cells, choose Number.
In Category, select General.
Press OK.

This is the output.

Step 2: Find the Total Value

Use the SUM function to determine the total number of participants.

=SUM(C5:C6)

Press Enter. 2000 is returned by the SUM function.

Apply the SUM function to calculate the total number of preferences:

=SUM(E5:E6)

Press Enter. 1280 is returned by the SUM function.

Step 3: Determine the Average Percentage

Choose E8 and enter the SUM function:

=E7/C7

Press Enter to see the average percentage: 64%.

Method 3 – Utilizing a Formula to Calculate the Average Percentage

This is the formula.

(((Percentage1*number1)+(percentage2*number2))/total number))*100

Here the Teachers are Number 1 and the Students are Number 2; the percentages are provided; the Total Number is the combined number of Teachers and Students.

Steps:

Select D8, and enter the formula:

=(C5*D5+C6*D6)/C7

Press Enter. The average percentage will be displayed in fractions. The fraction is 0.64.

Convert the fraction into a percentage. Press Ctrl + Shift + %.

Fractions are converted into percentages. The average percentage is 64%.

Calculate the Average Percentage of Marks in Excel

The sample dataset showcases six students and their respective scores in three tests. To calculate the Average Percentage, insert an extra column.

Steps:

Select F5. Enter the AVERAGE function:

=AVERAGE(C5:E5)

Press Enter. The AVERAGE function returns the average percentage: 77.6666667.

AutoFill the rest of the cells in column F.

Enter the formula in G5.

=F5/100

Press Enter to see the output: 0.78.
Here, F5/100 will show the average percentage of marks in fraction form.

AutoFill the rest of the cells in column G.

Convert the fraction into a percentage by pressing Ctrl + Shift + %.

Fractions were converted into percentages.

Bottom Line

#N/A! error arises when the formula or a function in the formula fails to find the referenced data.
#DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
You can press Ctrl + Shift + % on your keyboard to convert fractions into a percentage.

Download Practice Workbook

Download this practice workbook to exercise.

Calculate Average Percentage.xlsx