Calculate Average Percentage in Excel [Free Template+Calculator]

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.

In this article, I will discuss two effective methods for determining the average percentage.

Download Template & Calculator

All the two methods are available in the Excel workbook that I uploaded. The first method is in Sheet 1 and the other one is in Sheet 3. You might use the above two methods in excel as a calculator. For your convenience, I made two calculators for the two methods. It is available on Sheet 2 and Sheet 4 respectively.

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 which 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 percentage refers to 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.

Basics of Average Percentages

The average percentage is basically the mean value of percentages. The number of percentages maybe two or even more, it actually depends on the dataset.  For example, in an educational institution 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 When Calculating Average Percentage in Excel

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 D6 using the AVERAGE formula, though it is not correct.

Mistake when calculating average percentage

How to Calculate Average Percentage in Excel

The average percentage can be calculated using the following two methods. Whatever the methods, the output will be the same.

i. Calculating Average Percentage 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.

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

For this you have to follow:

1. Select cell D2

2. Right-click on the mouse button

3. Chose the Insert option

4. Press Table Column to the Right

Creating cell

5. Don’t worry if you haven’t created a table like me. In that case press Insert > Shift Cells Right

6. Now you have to determine the multiplication of percentage and number of participants. So Enter the Equal (=) symbol. Then put C3*D3

Determine the Preference in Number

Here, C3 is the number of participants for teachers, and D3 is the percentage of preference for teachers.

Using Fill Handle

7. Now use the Fill Handle Tool of Excel. Just drag the cursor from the lower-right part of the E3 to E4. And you can see such as the following-

Preference in number

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

1. Select the cell D3

2. Chose the Number command

3. Pick the General command from Format Cells

4. Enter Ok

Converting into decimal number

5. Now you can easily use AutoFill Options from Excel and choose Fill Formatting Only for filling the D4 cell.

Using fill formatting only

Step 2: Find the Total Value

In this situation, you have to use the SUM formula of excel for determining the total number of participants and the total number of preferences like the following.

Total Number

Step 3: Determine the Average Percentage

1. Chose the cell D6

2. Enter Equal sign

3. Put the formula E5/C5 where E5 is the total number of preferences and C5 is the total number of participants

4. Press Enter

5. The average percentage is 64%

Average percentage using method 1

ii. Calculating Average Percentage By Using a Defined Formula

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

Given sample size

The formula of the average percentage is:

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

Here the Teachers are Number 1 and 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

Step 1: Convert the Percentage into Decimal Value (e.g. 40% = 0.4) or Use the Existing Percentage

Step 2: Multiply the percentage and given Number (or sample size) (e.g. 800*0.4 for Teachers)

Step 3: Add the two products together (e.g. 800*0.4+1200*0.8)

Step 4: Divide the aggregated value by the whole number (or total sample size), in this case, 2000.

Step 5: Transform the value in percentage

All the above steps can be done in a single approach in excel using the average percentage formula like the following-

Select the cell D6

Enter the formula =(((C3*D3+C4*D4)/C5))*100 where C3 and D3 are the number and percentage of Teachers respectively. Similarly, C4 and D4 are for Students. And C5 is the total number of Teachers and Students.

Average percentage using formula

After implementing two methods of the almost same data, it has been found that the output is the same.

Conclusion

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

Further Readings

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