Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Average Percentage in Excel (3 Easy Methods)

Calculating the average percentage may appear to be an uncomplicated task. But it is not like the average where the total value is divided by the number of values. For that reason, sophisticated and authentic methods are essential. Today, in this article, I will discuss three suitable methods to calculate average percentage in Excel effectively with appropriate illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Percentage Basics

The word ‘percent’ refers to ‘for hundreds each’ and it is also expressed as the % symbol. A percentage is a quantity or ratio that is a fraction of 100. Divide the number by the whole number and multiply by 100 if we need to calculate the percent of a number. But the word ‘percentage’ also means “to be a part of a whole,” which can be expressed in hundredths.

The difference between percentage and percent isn’t clear to many of us. That’s why we often use these two words interchangeably. Generally, the percent encloses a specific number, whereas the percentage refers to the change between numbers.


Average Basics

In a set of numbers, the average value is the value, calculated by dividing the total by the number of numbers. For instance, the average of 5, 6, 8, 10, and 11 will be 8 where the sum of total values is 5+6+8+10+11=40 and the number of values is 5. To express a basic Average Formula, we can write,

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

Introduction to Average Percentages

The average percentage is basically the mean value of percentages. The number of percentages may be two or even more, it actually depends on the dataset.  For example, in an educational institution, the preference for sports in the case of teachers is 40% whereas in the case of students it is 80%. Now, what will be the average percentage? Actually, it is 64% and it will be discussed in this article how this percentage is calculated.


Common Mistakes While Calculating Average Percentage

Usually, we use the AVERAGE formula of MS Excel to determine the average of any quantities. If we calculate the average percentage in such a way, it will undoubtedly be wrong. So, be careful and avoid this mistake. As shown in the following figure, the average percentage is determined in cell D8 using the AVERAGE formula, though it is not correct.


3 Suitable Ways to Calculate Average Percentage in Excel

The average percentage can be calculated using the following three methods. Whatever the methods, the output will be the same. To calculate average percentages, we will use the SUMPRODUCT, SUM, and AVERAGE functions and Mathematical formulas. Here’s an overview of the dataset for today’s task.

how to calculate average percentage in excel


1. Combine SUMPRODUCT and SUM Functions to Calculate Average Percentage

In this section, we will apply the SUMPRODUCT and SUM functions to calculate average percentages in Excel. From our dataset, we can easily do that. Let’s follow the instructions below to learn!

Steps:

  • First of all, select cell D7 and write down the below SUMPRODUCT and SUM functions in that cell. The functions are,
=SUMPRODUCT(C5:C6,D5:D6)/SUM(C5:C6)
  • Hence, simply press Enter on your keyboard. As a result, you will get an average percentage which is the return of the SUMPRODUCT and SUM functions. The return is 0.64.

Combine SUMPRODUCT and SUM Functions to Calculate Average Percentage

  • Now, we will convert the fraction into a percentage. To do that, firstly select cell D7. Hence, from your Home tab, go to,

Home → Number → Percentage

  • As a result, you will be able to calculate average percentages which have been given in the below screenshot.

Combine SUMPRODUCT and SUM Functions to Calculate Average Percentage

Read More: How to Use Excel Formula to Calculate Percentage of Grand Total


2. Calculating Average Percentage by Using Data from A Survey

In the following dataset, we see that the number of participants surveyed and preference for sports in percentage are given. We have to find out the average percentage of preference for sports. It’s astonishing that we can determine our expected value simply if we proceed with the following steps. The steps are as follows:

Step 1: Determine the number that each percentage of preference denotes

  • First of all, select cell E5, and write down the below Mathematical formula in that cell. The Mathematical formula is,
=C5*D5
  • Now, press Enter on your keyboard. As a result, you will get the return of the Mathematical The return is 320.

Calculating Average Percentage by Using Data from A Survey

  • Hence, AutoFill the Mathematical formula to the rest of the cells in column E.

Note: You can do the same thing by converting the percentage into a decimal. For this, you have to proceed with the following steps-

  • Select cells D5 to D6. Press Ctrl + 1 on your keyboard.

Calculating Average Percentage by Using Data from A Survey

  • As a result, a Format Cells window pops up. From the Format Cells window, firstly, choose the Number Secondly, select the General option under the Category drop-down list. At last, press the OK option.

  • The result will remain unchanged.

Calculating Average Percentage by Using Data from A Survey

Step 2: Find the Total Value

  • In this situation, you have to use the SUM function of excel to determine the total number of participants.
=SUM(C5:C6)
  • Hence, press Enter on your keyboard. As a result, you will get the return of the SUM function. The return is 2000.

  • Similarly, apply the SUM function to calculate the total number of preferences like the following.
=SUM(E5:E6)
  • Further, press Enter on your keyboard. As a result, you will get the return of the SUM function. The return is 1280.

Calculating Average Percentage by Using Data from A Survey

Step 3: Determine the Average Percentage

  • Chose the cell After that, write down the SUM function in that cell. The SUM function is,
=E7/C7
  • Hence, press Enter on your keyboard, and you will get an average percentage. The average percentage is 64%.

Calculating Average Percentage by Using Data from A Survey

Read More: How to Use Data Bars with Percentage in Excel (3 Ideal Examples)


Similar Readings


3. Utilizing a Defined Formula to Calculate Average Percentage

In the following figure, we see a dataset of the given sample size and percentage. Now you have to calculate the average percentage.

The formula for the average percentage is:

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

Here the Teachers are Number 1 and the Students are Number 2. The percentages are also respectively given. The Total Number is the combined number of Teachers and Students.

Then you may proceed with the following steps to learn!

Steps:

  • First of all, select cell D8, and write down the below formula,
=(C5*D5+C6*D6)/C7
  • Hence, press Enter on your keyboard. As a result, you will get the return of the average percentage into fractions. The fraction is 0.64.

Utilizing a Defined Formula to Calculate Average Percentage

  • Now, we will convert the fraction into a percentage. To do that, press Ctrl + Shift + % on your keyboard.

  • As a result, you will be able to convert fractions into percentages. The average percentage is 64%.

Utilizing a Defined Formula to Calculate Average Percentage

Read More: Percentage Formula in Excel (6 Ideal Examples)


Calculate Average Percentage of Marks in Excel

The AVERAGE function is categorized under the Statistical functions in Excel. This function returns the average value of a given argument. From the description of the AVERAGE function, you might have understood that the basic use of this function is to find the average of several numbers in an Excel Sheet.

To show examples, we have brought a simple dataset of six students and their respective scores in three tests. For this, you need to insert an extra column of Average before the Average Percentage column. Let’s follow the instructions below to learn!

Steps:

  • First, we have to select cell F5. After that, write down the below AVERAGE function in that cell. The function is,
=AVERAGE(C5:E5)
  • Hence, simply press Enter on your keyboard. As a result, you will get an average percentage which is the return of the AVERAGE function. The return is 77.6666667.

Calculate Average Percentage of Marks in Excel

  • Hence, AutoFill the AVERAGE function to the rest of the cells in column F.

  • Again, write down the below formula in cell G5.
=F5/100
  • Hence, press Enter on your keyboard, and you will get the output of the formula. The output is 0.78.
  • Here, F5/100 will show the average percentage of marks in fraction form.

Calculate Average Percentage of Marks in Excel

  • Hence, AutoFill the Mathematical formula to the rest of the cells in column G.

  • Now, we will convert the fraction into a percentage. To do that, press Ctrl + Shift + % on your keyboard.

Calculate Average Percentage of Marks in Excel

  • As a result, you will be able to convert fractions into percentages which have been given in the below screenshot.

Calculate Average Percentage of Marks in Excel

Read More: How to Apply Percentage Formula in Excel for Marksheet (7 Applications)


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.

Conclusion

Now you have the above ways for calculating the average percentages, so you will have no trouble as far as I assure you. Thank you for reading the article carefully. Please share your opinions and leave comments below.


Related Articles

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo