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.
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.
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.
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.
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.
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