Pivot Tables are an amazing built-in reporting tool in Excel. While typically used to summarize data with totals, you can also use them to calculate the percentage of change between values. Even better: It is simple to do.
1 Calculate Two Columns in Excel
2 Calculate Only 40 of 48 Hours in Excel
3 Calculate the Gross and Net Margins in Excel
4 Calculate the Decline in Sales by Percent for the Number of Years in Excel
Averages are typically computed as the sum of values divided by the number of data points. However, averages are also calculable from percentages. As an example, grades in a course might be weighted differently between tests. If that's the case, a simple average would bias the result. Instead, you would use the percentage weight when calculating the average. By constructing an Excel spreadsheet, this calculation becomes a simple matter of data entry.
2.
Enter the data to be averaged in column A. For example, if you had three regular tests, a midterm and a final, you might enter grades of 85, 100, 90, 80, 95 in cells A1 through A5.
3.
Enter the corresponding percentages in column B. In the example, if regular tests were worth 10 percent each, the midterm was worth 20 percent and the final 50 percent, then you'd enter 10%, 10%, 10%, 20% and 50% in cells B1 through B5. Be sure to include the percentage symbol so Excel knows they are percentages.
4.
Enter '=A1*B1' without quotes in cell C1. In the example, this multiplies 10 percent times 85 to get 8.5.
5.
Click the lower right corner of cell C1 and drag to the last cell in column C, which corresponds to the last data entry in columns A and B. This copies the formula to the selected cells. In the example, you would drag your mouse down to cell C5.
6.
Enter '=sum(C:C)' without quotes in cell D1 to calculate the average using percentages.
Photo Credits
Thomas Northcut/Photodisc/Getty Images
Cite this Article
Choose Citation Style
Taylor, C.. 'How to Do Averages With Percentages in Excel.' Small Business - Chron.com, http://smallbusiness.chron.com/averages-percentages-excel-29969.html. Accessed 20 June 2019.
Taylor, C.. (n.d.). How to Do Averages With Percentages in Excel. Small Business - Chron.com. Retrieved from http://smallbusiness.chron.com/averages-percentages-excel-29969.html
Taylor, C.. 'How to Do Averages With Percentages in Excel' accessed June 20, 2019. http://smallbusiness.chron.com/averages-percentages-excel-29969.html
Note: Depending on which text editor you're pasting into, you might have to add the italics to the site name.