We generally use the weighted average in those places where calculating the normal average would give a false result. Using the weighted average formula in Google Sheets is pretty straightforward. There is a function built in at Google Sheets that give the weighted average directly. The function is called **the AVERAGE.WEIGHTED function**. We can also use the combination of the **SUMPRODUCT** and **SUM** functions to calculate the weighted average in Google Sheets. In this article, I’ll show you 5 suitable examples to use the weighted average formula in Google Sheets with clear steps and images.

**Table of Contents**hide

**A Sample of Practice Spreadsheet**

You can download Google Sheets from here and practice very quickly.

**5 Suitable Examples to Use Weighted Average Formula in Google Sheets**

Let’s get introduced to our dataset first. Here we have some students in **Column B** and their test scores in **Column C** and **Column D**. Separate weights have been given on each test score in **Column C** and **Column D** as we can see in the picture. Now we have to calculate the final score of every student. The final score would be the weighted average of their 2 separate tests. So we’ll see 5 useful examples to use the weighted average formula in Google Sheets.

**Example 1. Using AVERAGE.WEIGHTED Function with Weight in Percentage**

In our dataset, the weights are given as percentages. So now we’ll see how to calculate the weighted average of every student’s score using the **AVERAGE.WEIGHTED** function. This function does 2 things together. First, it multiplies each test score by its weight. Then it simply divides them with the summation of the weights. Finally, we’ll get our desired output very quickly.

**Steps:**

- Firstly, type the following formula in
**Cell E5**–

`=AVERAGE.WEIGHTED(C5:D5,$C$12:$D$12)`

- Secondly, hit
**Enter**to get the weighted average.

- Finally, you’ll get the final scores of all the students, which are the weightage averages of 2 test scores.

**Example 2. Applying AVERAGE.WEIGHTED Function with Extra Values**

Additionally, we can extend the **AVERAGE.WEIGHTED** function if we have extra values like in our case. At this time we have bonus score and bonus weight in **Column C** and **Column D**. We want to add them to our calculation. So putting these values simply into our formula will do the job. Let’s see how to do that.

**Steps:**

- At first, write the next formula in
**Cell E5**–

`=AVERAGE.WEIGHTED(C5:D5,$C$12:$D$12,$C$14,$C$15,$D$14,$D$15)`

- Then, press
**Enter**to get the final score.

- At last, use the
**Fill****Handle**tool to apply the formula in all cells. In the end, you’ll get your desired result.

**Example 3. Inserting AVERAGE.WEIGHTED Function with Weight in Quantity**

Look at some different problems this time. We have some products in **Column B**, their prices in **Column C,** and their quantity in **Column D**. Now, we’ll use those quantities as weight and we want the total weighted average price of those products. Let’s solve this problem using **AVERAGE.WEIGHTED** function.

**Steps:**

- First, insert the below formula into
**Cell C12**–

`=AVERAGE.WEIGHTED(C5:C10,D5:D10)`

- Ultimately, hit the
**Enter****Button**to obtain the weighted average price of the products.

**Example 4. Combining SUMPRODUCT and SUM Functions**

Moreover, we can use the combination of **SUMPRODUCT** and **SUM** functions to calculate the weighted average in Google Sheets. The **SUMPRODUCT** function will first multiply each test score by its weight and add them together. Then we’ll divide these products with the summation of the weights using the **SUM** function. This process is a bit longer because we have to use 2 functions here.

**Steps:**

- Before all, type the next formula in
**Cell E5**–

`=SUMPRODUCT(C5:D5,$C$12:$D$12)/SUM($C$12:$D$12)`

- Consequently, press the
**Enter****Button**to get the weighted average of the scores.

- Then, apply the
**Fill****Handle**tool to get the output in the whole column.

- Finally, we’ll get our final scores like this picture.

**Example 5. Inserting AVERAGE.WEIGHTED Function in Pivot Table**

We use the **Pivot Table** in Google Sheets to calculate and analyze data quickly. We can insert the **AVERAGE.WEIGHTED** function in **Pivot Table**. Here we have a dataset having students’ names in **Column B**, their test no in **Column C**, their scores in **Column D**, and their weight in **Column E**. And we also have a **Pivot Table** having only the unique students’ names in **Column B**. Now we’ll see how to calculate the weighted average using **Pivot Table** in Google Sheets.

**Steps:**

- Earlier on, click on the
**Edit Button**situated under the**Pivot Table**to go to**Pivot Table Editor**as shown in the picture.

- Moreover, in the
**Pivot Table Editor Box**click on**Add**under the**Values**menu and add**Score**,**Weight**, and**Calculated Field.**

- Consequently, you’ll get a
**Pivot Table**like this. - Now, we’ll calculate the weighted average in the
**Calculated Field 1 Column**.

- Again go to
**Pivot Table Editor Box**and type the following formula in the formula box under the**Calculated Field 1**menu-

`=AVERAGE.WEIGHTED(Score,Weight)`

- Next select
**Custom**under**Summarize By**menu.

- In the end, we’ll get the weighted average in the
**Pivot Table**. - Don’t forget to rename
**Cell E14**as**Weighted Average**.

**Weighted Average Calculator**

Last but not least we can create a weighted average calculator in Google Sheets. In that calculator when we put any values and their weights, it’ll give results directly. Below you’ll find the steps for creating the weighted average calculator.

**Steps:**

- Initially, type the following formula in
**Cell C12**–

`=AVERAGE.WEIGHTED(B5:B10,C5:C10)`

- Then, hit the
**Enter****Button**to get something like the picture.

- When we put any values in this calculator, it’ll produce results automatically. As we haven’t put any values it is showing an error message. Nothing to worry about for this.

**Conclusion**

That’s all for now. Thank you for reading this article. In this article, I have discussed how to use the weighted average formula in Google Sheets with 5 useful examples. I have also discussed creating a weighted average calculator. Please comment in the comment section if you have any queries about this article. You will also find different articles related to google sheets on our **officewheel.com****.** Visit the site and explore more.