Excel's Weighted Average Using Three Arrays
Microsoft Excel simplifies the process for a wide variety of mathematical methods with the implementation of the status bar. The status bar includes links to many functions, including an "AVERAGE" function. Create a manual weighted average with formulas, but limit your input to two data array columns. An attempt to create a weighted average from three columns does not produce a true average. The third column may cause inaccuracies in the calculations.
Other People Are Reading
-
Weighted Average
-
A weighted average is a calculation that determines the average of a set of numbers when certain values are worth more to the calculation than others. For example, students whose homework and tests are worth different values toward the final course grade have a weighted course average. Using the weighted average provides a more accurate calculation of the average when certain figures are out of the ordinary or based on a significantly different amount than the rest. In the case of student grades, quizzes may be worth 50 percent of the score while tests and homework are split at 25 percent each. Calculating a straight average does not make the quizzes a higher value but a weighted average lets you determine how important each category is.
Weighted Average Calculation
-
To calculate the weighted average of a purchase in Excel, place the information for the amount paid in one column and the information for the quantity purchased in another column. If you place the cost in the first two cells of column B and the quantity in the first two cells of column C, the formula to create a weighted average would appear as "=sum((B1*C1)+(B2*C2))/SUM(C1:C2)."
-
Third Value Array
-
If you attempt to add a third array to the weighted average section, the calculation will be inflated by the additional figures. The weighted average is an average based on one additional factor which means two arrays in the Excel spreadsheet calculation.
Excel Functions
-
There are several additional functions in the Excel application that can help you calculate averages. The simple, straightforward "SUM" calculation is the common approach, though the "AVERAGE" function can assist if you are determining a straightforward average. There are variations of the "AVERAGE" command such as "AVERAGEIF" that only calculates an average if predefined conditions are met. "AVERAGEA" can produce an accurate average even if your source data is mixed with values and text. The "AVERAGEA" command tells Excel to treat cells that return an error or contain text as a zero value.
-
