Weighted Average in Excel – Shiksha Online

This tutorial will teach you how to calculate a weighted average, also known as a weighted mean.

The weighted average in Excel is one of the most critical inventory valuation methods since it is used to determine the cost of sale if we have implemented a permanent inventory system.

Before continuing, let’s look at a definition of weighted average in Excel.

Content

• What is the Weighted Average?
• How to Calculate Weighted Average in Excel
• Weighted Average Example
• Weighted Average with SUMPRODUCT and SUM functions
  • Example 1: SUMPRODUCT
  • Example 2: SUMPRODUCT and SUM

What is the Weighted Average?

A weighted average is an average to the most identical figure or closest to the arithmetic mean. Usually, when calculating an average, all the numbers have the same weightage. The numbers are added together and divided by the number of values. Each value is assigned a weightage with a weighted average, so some values ​​influence the result more than others.

We can say that the weighted average is used when, within a series of data, one of the values ​​has additional importance, or there is data with greater weight than the rest. In addition, it helps us establish the said weight through the method known as weighting and use this value to perform the average calculation.

How to Calculate Weighted Average in Excel

We can use the SUMPRODUCT and SUM functions to calculate the weighted average in Excel. Note that no default Excel function calculates the weighted average. However, we can make use of the functions mentioned above to do the calculation of a weighted average in Excel.

Read all our MS Excel Tutorials

Weighted Average Example

To make this concept clear, let’s look at the following example. Suppose a company has sold 10,000 technology products in a single day. 

• 3,000 cell phones were sold at a price of Rs. 60,000
• 1,500 laptops at Rs. 90,000
• 1700 headphones at Rs. 20,000
• 800 tablets at Rs. 800,000
• 3000 computers at Rs. 80,000

What is the weighted average price of the 10,000 products?

The first thing we must do is determine the relative weight of the units sold. To obtain this data, we only have to divide the units sold by the total sold, as shown in the following image.

To calculate the weighted average price of product sales, we only have to multiply the price by the weighting obtained, that is, what we have calculated as the relative weight of the units sold, and add the results obtained.

However, we can simplify this process; the idea is that we do not have to do this manually and that it is Excel itself, which helps us be more efficient and productive when working in the spreadsheet. Therefore, we will show you how to do the same operation with a single formula.

Weighted Average with SUMPRODUCT and SUM functions

If we want to obtain the weighted average, we only have to use the combination of the SUMPRODUCT and SUM functions. And in the example where we have calculated the weighting, we can use the SUMPRODUCT function, and that’s it. But let’s see the two cases to have more clarity and options when working with the weighted average in Excel.

Example 1: SUMPRODUCT

Let’s first see the case where we have the weighting data, remember that we have obtained this data (weighting) by dividing the units over the total number of units sold.

To calculate the weighted mean, we would only have to use the SUMPRODUCT formula –

=SUMPRODUCT (array1; [array2] for the proposed case, our formula would be as follows: 

=SUMPRODUCT (Price, Weightage)

=SUMPRODUCT(B2:B6,D2:D6)

The SUMPRODUCT function has a punctual characteristic since it behaves like two functions. On the one hand, it performs the multiplication of the values ​​of the given array, and then it does the sum of all those values.

Must Explore – MS Excel Courses

Example 2: SUMPRODUCT and SUM

Now let’s look at the case where we can find the weighted mean using the SUMPRODUCT function and SUM. For this exercise, we will only have the specific data of the price at which we have sold the products and the units sold of each item or product.

General Formula

=SUMPRODUCT(array1, array2)/SUM(array2)

For our example, the formula would be as follows:

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

Observe how the result obtained with the SUMPRODUCT and SUM function has been the same as the previous options that we used to get the weighted average. Just that we did not have to calculate the weightage of units.

Hope this article helped you understand how weighted average in Excel works.

Top Trending Articles in MS Excel:

Most Useful Excel Formulas | Min Max Functions in Excel | Average Functions in Excel | Introduction to MS Excel | Financial Modelling in Excel | MS Excel interview questions | Sum Function in Excel | Trim Function in Excel | Pivot Table in Excel | Percentage in Excel | Vlookup in Excel | Median Function in Excel | Types of Charts in Excel | Count Function in Excel | MS Excel Vs. Google Sheet | Remove Duplicates in Excel | Create Graph in Excel