Average vs Weighted Average

Difference Between Average vs Weighted Average

We often come across the two terms, average and weighted average. Average is the sum of all values divided by the total number of values. Alternately, the weighted average is multiplying each value with a weight and dividing by the sum of the weights. An average treats all inputs equally; a weighted average does not.

To understand it more clearly, let us look at an example. For example, suppose you have the marks secured by a student in four subjects. You can find the student average using the AVG function, while his weighted average can be found using each subject’s weight using the formula as shown below:

=(C2*B2+C3*B3+C4*B4+C5*B5)/1

Difference Between Average vs Weighted Average

Both average and weighted average are significant in financial analysis. The average is the simple sum of all values divided by the number of values, while the weighted average multiplies each value with its corresponding weight.
The weighted average is helpful when each value has a different weightage and must be considered based on it. Here, the weighted average tends to shift towards the values with more weightage.
The AVERAGE function in Excel calculates the average, while the SUMPRODUCT and SUM formulas are used for the weighted average.

What is Average?

In Excel, the AVERAGE function calculates the average, or the arithmetic mean of a given set of numbers. The function can hold up to 255 arguments. These may be numbers, cell references, constants, or ranges. The syntax of the AVERAGE function is as follows:

=AVERAGE(number1, [number2], …)

Arguments

number1 – This is usually a cell reference or a number.
number2 – [optional] A number or cell reference

Example of Average

Let us look at how to use the AVERAGE function in Excel. First, we have an example where we consider the number of goals scored by Lionel Messi and Christiano Ronaldo across four seasons starting from 2019 up to 2023.

Average vs Weighted Average - Average Example

Now, let us find the average goals scored in these four seasons by the two football greats. We use the formula =AVERAGE(B2:B5) and =AVERAGE(C2:C5) for Messi and Ronaldo, respectively.

Thus, you can find that Christiano Ronaldo has a better average than Lionel Messi.

What is the Weighted Average?

A weighted average considers the weight given to each value. It is calculated as the sum of all values multiplied by their weight and divided by the sum of the weights. For instance, during an employee’s performance evaluation, the company may consider different parameters like quality of work, punctuality, and time management with different weights. In Excel, the weighted average is usually calculated using the SUMPRODUCT and SUM functions.

The weighted average is used in the following scenarios.

Investors can weigh share prices by the number of stocks purchased to reflect the changing prices.
A teacher weights the various projects, exams, quizzes, etc., to reflect varying difficulty.
A company may use different weights or values for other parameters during evaluating and rewarding an employee.

Example of Weighted Average #1

In this example, let us look at an investor who has acquired a company’s stocks over two years. He acquired 200 stocks at $20 in the first year and 100 at $40 in the second. Now, we must get a weighted average of the price paid by the investor.

Let us look up the table containing the details.

Average vs Weighted Average - Weighted Average Example 1

Step 1: To calculate the weighted average, we must multiply the total stocks by their values and divide it by the number of shares acquired over the two years.

For this, you can use two main functions, SUMPRODUCT and SUM. The SUMPRODUCT adds up arrays and returns the total of the products. To find the weighted average, we enter the following formula in cell C6.

=SUMPRODUCT(B3:B4,C3:C4)/SUM(B3:B4)

B3:B4 represents the range with the number of stocks bought each year, while their prices are in the range, C3:C4. Finally, we divide with the total number of stocks obtained using SUM.

Thus, the weighted average price per stock is obtained as $26.67.

Example of Weighted Average #2

Let us look at another example of a weighted average cost calculation. A furniture dealer purchases 100 chairs for $10/unit. Next month he purchases another 200 chairs for $20 a unit.

Average vs Weighted Average - Weighted Average Example 2

Step 1: Now, the total number of chairs purchased is 500. Seventy-five chairs were sold at the accounting period end.

To calculate the weighted average costs, let us apply the following formula:

Let us suppose that there were sales of 80 chairs. The cost of the good sold: 80*$16.67 = $1333.6
The remaining inventory is $5000-$1333.6 = $3666.4.

It is a method that considers the fluctuations in inventory costs and averages the cost over the respective period, thereby helping businesses minimize the impact of sharp price fluctuations. We also better represent the cost of the sold goods using the weighted average method.

Average vs Weighted Average – Key Differences

In average vs. weighted average, the average is the sum of all values divided by the number of values. In contrast, the weighted average multiplies all the numbers by their weights and is divided by the sum of the weights.

We use the average when simple data sets with no weightage are involved. However, we widely use the weighted Average in financial analysis as a more accurate method when complex data sets are involved.

Excel has a direct formula called AVERAGE for finding the mean average, while Excel has no straightforward formula to find the weighted average.

Average vs Weighted Average – Comparison Table

Let us look at the comparison table for Average vs Weighted Average.

Average	Weighted Average
The sum of all the observations in the data set divided by the number of observations gives the mean average.	The Weighted average considers the varying degrees of importance of the observations in a data set.
The formula to calculate the average in Excel is AVERAGE.	The formula to calculate the weighted average in Excel is: SUMPRODUCT(weights, values)/SUM(weights).
All observations have equal value.	More value is given to the observations that occur more.
For complex sets of data, this is a less accurate method. It is used in simple data analysis situations.	This is best suited for complex data sets as it considers the relative importance of all values.
It is effortless to calculate	It requires some time and effort to calculate
It is the arithmetic mean or center point.	It is more inclined towards observations with greater weightage.

Download Template

This article must help understand Average vs Weighted Average, with their formulas and examples. You can download the template here to use it instantly.

Average vs Weighted Average Download

Average vs Weighted Average