We can use a formula that combines the **SUMPRODUCT** **function **and the **SUM function** to **calculate the weighted average** of a set of values in a **range**. The steps below will walk through the process.

*Figure 1- Final result of the Weighted Average*

**General Formula**

**=SUMPRODUCT(values,weights)/SUM(weights)**

**Values:**These are the numbers in the data**Weights:**This is the percentage weight of each number in the data

**Formula**

`=SUMPRODUCT(B4:B11,C4:C11)/SUM(C4:C11)`

**Setting up the Data**

We will calculate the weighted average score of the courses in the data below.

**Column A, B,**and**C**will contain the**Course**,**Score**, and**Weight**respectively- The
**mean or normal average****score**for the courses will be returned in**Cell E7**. The**weighted average**will be returned in**Cell E4**

* Figure 2 – Setting up the Data*

**Weighted Average**

- We will click on
**Cell E4** - We will insert the formula below into
**Cell E4**

**=SUMPRODUCT(B4:B11,C4:C11)/SUM(C4:C11)** - We will press the
**enter**key

* Figure 3- Weighted Average Score for the Courses*

**Normal Average**

- We will click on
**Cell E7** - We will insert the formula below into
**Cell E7**

**=AVERAGE(B4:B11)** - We will press the
**enter**key

* Figure 4- Average Score for the Courses*

**Explanation**

The **SUMPRODUCT function** multiplies the cells in **Column B** with cells of **Column C (B4*C4** and so on) and returns the sum of each multiplied cells. This sum is then divided by the **sum of the scores** that have been added by the **SUM function.**

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment