How to Do Averages With Percentages in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
In a weighted average, some numbers count more than others.
Image Credit: Thomas Northcut/Photodisc/Getty Images

An average with percentages gives you a weighted average. Because these percentages reflect the relative importance of the items being averaged, you get a more accurate view of the situation. Suppose you sell three kinds of widgets: red, blue and green. Your first-quarter net profits are $200, $150 and $25, respectively. You want to know your average profit, but the sales volume of each kind of widget differs: 30 percent, 50 percent and 20 percent, respectively. You need to weight each amount by its percentage of the total sales.

Advertisement

Step 1

Open a new spreadsheet in Excel.

Video of the Day

Step 2

Label the data in row 1 of the spreadsheet. In this case, enter "Red Widgets," "Blue Widgets," "Green Widgets" and "Average Profit" (omit the quotation marks here and throughout) in cells A1 through D1.

Advertisement

Step 3

Select row 4 by clicking the row header. On the Home tab, select "Currency" from the drop-down list in the Number area. This will ensure that your final figures display as dollars and cents.

Step 4

Enter the figures you want to average in row 2 of the spreadsheet. For this example, use the net profits from the widgets: "200," "150" and "25" in cells A2, B2 and C2.

Advertisement

Step 5

Enter the percentages by which you want to weight each figure in row 3 of the spreadsheet. For our widgets, it will be "30%," "50%" and "20%" in cells A3, B3 and C3.

Advertisement

Step 6

Enter the formula "=A2*A3" in cell A4. This represents the profits from red widgets in proportion to total sales. The result should be $60.00.

Advertisement

Step 7

Select cell A4 and press "Ctrl-C" to copy the formula.

Step 8

Select cells B4 and C4 and press "Ctrl-V" to paste the formula. Cell B4 should then display "$75.00" and cell C4 should display "$5.00."

Step 9

Enter the formula "=sum(A4:C4)" in cell D4. This results in the weighted average profit for the quarter: $140.00.

Video of the Day

Advertisement

Advertisement

references