How to Calculate Average Percentage in Google Sheets

If you’re working with percentages in Google Sheets and need to find their average for analysis or reporting, this article can help you. Discover just how easy it is to calculate the average percentage in Google Sheets.

Calculating the average of percentages is a common task, especially when analyzing datasets containing rates, proportions, or completion levels. Unlike simple numerical averages, when dealing with percentages, it’s crucial to ensure the calculation reflects the underlying values correctly. This is particularly important when the percentages are derived from different denominators.

We’ll use a simple dataset containing percentages of some sales figures shown in Column E to demonstrate how to calculate the average percentage in Google Sheets. This quick and straightforward method is ideal when each value holds equal weight. We can achieve results by following the simple function of AVERAGE.

Steps:

➤ Select the output cell D12 where the result will appear.
➤ Into the selected cell, type the formula:

➤ Or specify the range of cells containing the percentages by dragging your mouse over the cells from D2 to D11.

➤ Make sure the resultant average is formatted as a percentage. Select the cell > Go to the Format menu > choose Number > select Percent.

If you have numerical data, you must calculate percentages before finding the average. In this case, our dataset has only numerical data in columns B and C as “Previous Sales” and “Current Sales”. We’ll calculate percentages for each row in column D.

Steps:

➤ Select a new column D to calculate the percentage for each row.  Use the formula in the first cell:

➤ Then drag the fill handle down to apply the formula for the rest of the rows. This will show the percentage increase in column D.

➤ Format the result as a percentage by selecting the cell, going to Format > Number > Percentage

➤ Selecting D12 below the formatted column D, use the function:

➤ Press Enter to get the average percentage.

Frequently Asked Questions

How to calculate the average in different sheets?

Use the AVERAGE( ) function with references to cells on other sheets. For example:
=AVERAGE( Sheet1!A1:A10, Sheet2!B1:B5)

How to calculate the average percentage if there are non-numeric cells in the percentage range?

The non-numeric cells can be ignored by using the AVERAGE( ) function (including blank cells and text).

How can I get a weighted average percentage?

You’ll need to multiply each percentage by its corresponding weight, sum these products, and then divide by the sum of the weights. Simply use the formula:
=SUMPRODUCT (pectanges_range, weights_range) / SUM (weights_range))

What’s the formula for calculating a basic percentage in Google Sheets?

The basic formula for calculating a percentage is (Part/Total)*100. However, if you format the cell as a percentage (using Format Number Percent), you can often just use the formula Part/Total, and Google Sheets will handle the multiplication by 100 and display the result with the percent sign.

How to round percentages in Google Sheets?

You can use the ROUND function, =ROUND (cell,2)*100 will do it for you. Don’t forget to add a percentage sign in a new cell so the final answer is in percentage. Or if your column is already labeled percentage, it should be enough.

Wrapping Up

This article has demonstrated two clear methods for calculating the average percentage in Google Sheets. Whether you have existing percentage data or need to derive them from numerical values, Google Sheets offers the necessary tools for efficient calculation. Feel free to practice with your datasets.