Weighted averages are a powerful tool in data analysis, allowing you to assign different levels of importance or significance to different values within a dataset. This article will help you understanding the concept of weighted averages, explaining their significance and few practical applications. Additionally, we will explore all possible methods for calculating weighted averages in Excel – using VBA code and the built-in formulas.

Calculate weighted average using the SUMPRODUCT Function

The SUMPRODUCT function is a useful tool that can be employed to calculate a weighted average in Excel. Here’s the basic syntax:

=SUMPRODUCT(range1, range2) / SUM(range2)

Example:

Suppose you have a set of grades for a class, and each grade has a corresponding weight. In cells A2:A5, you have the grades (B, C, A, B), and in cells B2:B5, you have the weights (2, 1, 3, 2). To find the weighted average, you would use the formula:

Weighted Average – SUMPRODUCT()

=SUMPRODUCT(A2:A5, B2:B5) / SUM(B2:B5)

Using above formula, weighted average is calculated.

Calculate weighted average using custom VBA Function

We’ll explore how to calculate weighted averages using VBA in Excel, providing a detailed step-by-step guide along with examples to demonstrate the process.

Step 1: Accessing the VBA Editor:

Before diving into the code, you need to access the VBA editor in Excel. To do this, press Alt + F11 to open the editor. Once inside, you can create a new module by right-clicking on any of the items in the Project Explorer, selecting “Insert,” and then choosing “Module.” You can refer this article to know more about – how to add VBA code in Excel Sheet.

Step 2: Writing the VBA Code for calculating Weighted Average

Now, let’s create a VBA function that calculates the weighted average. This function code can be used as custom excel formula too. To know more about – how to create custom formulas or user defined function (UDF) in Excel VBA.

What is UDF?

Function WeightedAverage(rngValues As Range, rngWeights As Range) As Double
    Dim total As Double
    Dim weight As Double
    Dim i As Integer
    For i = 1 To rngValues.Cells.Count
        total = total + rngValues.Cells(i).Value * rngWeights.Cells(i).Value
        weight = weight + rngWeights.Cells(i).Value
    Next i
    If weight <> 0 Then
        WeightedAverage = total / weight
    Else
        WeightedAverage = 0
    End If
End Function

This function takes two input ranges, one for values and the other for weights. It iterates through each cell, calculates the weighted sum, and then divides it by the sum of weights.

Step 3: Using the VBA Function in Excel:

Once the VBA function is written, you can use it just like any other Excel function. For example, if you have values in column A (A2:A5) and weights in column B (B2:B5), you can use the function in a cell like this:

=WeightedAverage(A2:A5, B2:B5)

Your Playground: Excel Workbook

Now in the below excel workbook, you can play around with the weighted average calculation.

Real-World Applications:

1. Financial Portfolio Management: Calculating the weighted average return on investment for a diversified portfolio.
2. Educational Grading Systems:Determining the weighted average grade for a student based on different subject weights.
3. Supply Chain Management:Analyzing the weighted average delivery time for products based on supplier performance.
4. Employee Performance Appraisals:Calculating the weighted average performance rating for employees with different job responsibilities.