Weighted Average in Google Sheets

What is Weighted Average in Google Sheets?

The weighted average in Google Sheets can be described as an average where each quantity’s average is to be found in an assigned weight. Thus, the weighted average in Google Sheets is obtained by multiplying each value with a weight and dividing by the sum of the weights. Weighted averages are used in areas where the relative importance or frequency of different values is to be considered.

To understand it clearly, let us look at an example. For example, suppose you have the marks secured by a student in five subjects. You can find the weighted average in Google Sheets using each subject’s weight from the formula as shown below:

=SUM(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6) / SUM(C2:C6)

Weighted Average in Google Sheet - Sample
Key Takeaways
  • Weighted average is significant in financial analysis. The weighted average multiplies each value with its corresponding weight.
  • The formula to calculate the weighted average is: =SUMPRODUCT(weights, values)/SUM(weights) or =AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights]).
  • The weighted average in Google Sheets 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 Google Sheets calculates the average, while the SUMPRODUCT and SUM formulas besides the AVERAGE.WEIGHTED are used for the weighted average.

How to Calculate Weighted Average in Google Sheets? 

The weighted average is an average where each quantity whose average is to be found has a weight assigned. These weightings determine the relative importance of each quantity.

Using the SUMPRODUCT function 

A weighted average in Google Sheets 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. Use the SUMPRODUCT and the SUM functions to find a Weighted Average in Google Sheets in the following way.

Step 1: Enter all the details in a Google sheet. Now, enter the following formula in cell B7.

=SUM(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6)/SUM(C2:C6)

Weighted Average in Google Sheet - Method 1 - Step 1

Step 2: Press Enter. You get the weighted average of the student’s grade.

Here, SUMPRODUCT multiplies the two arrays together and returns the sum of the products. 

  • This formula multiplies each value by its weight and combines the values.
  • SUM(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6)
  • Afterward, you divide the SUMPRODUCT by the sum of the weights for your weighted average.
  • Hence, SUM(C2:C6)

Thus, you can get the weighted average in Google Sheets in a simple manner using SUMPRODUCT.

Using the AVERAGE.WEIGHTED function

The AVERAGE.WEIGHTED function calculates the weighted average in Google Sheets of a set of values directly compared to using the SUMPRODUCT function. This Weighted average Google Sheets formula includes the values and their corresponding weights. Let us look at how to do it below.

Its syntax is as follows:

=AVERAGE.WEIGHTED(values, weights, [additional values], [additional weights])

Here, 

  • values whose weighted average is to be found.
  • Weights is the corresponding list of weights.
  • [additional_values] Additional values to calculate the average.
  • [additional_weights] their additional corresponding weights to apply.

Let us use the weighted average function in Google Sheets for the same example as above.

Step 1: Enter the following formula in cell B8.

=AVERAGE.WEIGHTED(B2:B6,C2:C6)

Here, we are entering the range to be weighed and its corresponding weights.

Weighted Average in Google Sheet - Method 2 - Step 1

As can be seen, the function of calculating the weighted average is straightforward.

Examples

Let us look at a few simple examples of how to implement the weight average calculation in different ways.

Example #1 – Using the SUMPRODUCT function

In this example, let us look at an investor who has acquired a company’s stocks over five years. He acquired 300 stocks at $30 in the first year and 100 at $40 in the second. The details of the other years are in the table below. Now, we must get a weighted average of the price paid by the investor.

Let us look up the table containing the details.

Weighted Average in Google Sheet - 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 five 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 C7.

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

B2:B6 represents the range for the number of stocks bought each year, while their prices are in the range C2:C6. Finally, we divide by the total number of stocks obtained using SUM.

Weighted Average in Google Sheet - Example 1 - Step 1

Example #2 – Using the AVERAGE.WEIGHTED function

Let us look at another example of a weighted average calculation. A book vendor purchases 200 chairs for $5 per book. Next month, he purchases 300 more books for $7 a unit. Let us calculate the weighted average.

Weighted Average in Google Sheet - Example 2

Step 1: Now, find the total amount paid per month. Multiply the two columns.

Weighted Average in Google Sheet - Example 2 - Step 1

Step 2: To calculate the weighted average costs per month, let us apply the following formula.

=AVERAGE.WEIGHTED(C2:C3,B2:B3)

Weighted Average in Google Sheet - Example 2 - Step 2

Step 3: Press Enter. 

This function gives a better representation of the cost of the goods bought per month using the weighted average method. 

Weighted Average in Google Sheet - Example 2 - Step 3

Example #3 – Using Weighted Average Cost of Capital (WACC) formula

WACC represents a company’s cost of capital, taking into consideration each category of capital, such as debt and equity. It shows the average rate a company pays to finance its business.

In this example, a soft drink brand has a debt of $2 million and a market share of its equity of $4.5 million. 

Let us assume the minimum return demanded by the shareholder is 12%. Let us calculate the Weighted Average Cost of Capital. The formula is

WACC = (E/V) x Re + (D/V) x Rd x (1 – Tc) 

 Where:

  1. E: Firm equity’s market value
  2. D: Firm debt’s market value
  3. V: E + D
  4. Re: Cost of equity
  5. Rd: Cost of debt
  6. Tc: Corporate tax rate 

Step 1: Let us calculate E/V. Here, it is $4,500,500/$6,500,000.

Weighted Average in Google Sheet - Example 3 - Step 1

Step 2: The value is 0.69. Let’s multiply this by the shareholder’s demand of 12%.

0.69*0.12 = 0.0828

Weighted Average in Google Sheet - Example 3 - Step 2

Step 3: Now, the weighted cost of debt is calculated using D/V.

Debt = D/V = $2,000,000/$6,500,000

Weighted Average in Google Sheet - Example 3 - Step 3

 In this case, that’s 0.3.

Next, multiply it by the company’s cost of debt, which is, say, 4%. Let us multiply the product of those two numbers by one minus the tax rate. If the tax rate is 0.20, 1 – the tax rate is equal to 0.80. Therefore:

Weighted cost of debt = (0.3 × 0.04) x 0.80 = 0.0096

Weighted Average in Google Sheet - Example 3 - Step 3 - debt

Adding those two numbers together gives the weighted average cost of capital.

WACC = 0.0828 + 0.0096 = 0.0924 = 9.24%

Weighted Average in Google Sheet - Example 3 - Step 3 - WACC

This value represents the beverage company’s average cost to attract investors and expected returns, in addition to its financial strength.

Example #4

To finally nail the topic, let us consider another example of employee performance evaluation. Factors given weighted include Quality of work, submission on time, and teamwork.

Their weights are 60%, 20%, and 20%, respectively. 

Let us calculate the overall performance score of some employees. We have entered their details as shown.

Weighted Average in Google Sheet - Example 4

Step 1: The following formula is entered in cell C6 to find the overall performance of Person 1.

=SUMPRODUCT(B3:B5, C3:C5) / SUM(B3:B5)

Weighted Average in Google Sheet - Example 4 - Step 1

Step 2: Press Enter.

Weighted Average in Google Sheet - Example 4 - Step 2

Step 3: Now, to drag the formula across for the other employees, let us make Column B as absolute references as the weights do not change for each employee. Enter the formula in cell C6 and drag across to D6.

=SUMPRODUCT($B$3:$B$5, D3:D5) / SUM($B$3:$B$5)

Weighted Average in Google Sheet - Example 4 - Step 3

You get the weighted average performance of each employee.

Important things to Note

  • The Weighted average considers the varying degrees of importance of the observations in a data set.
  • More value is given to the observations that occur more.
  • It is best suited for complex data sets as it considers the relative importance of all values.
  • It requires some time and effort to calculate.
  • The weighted average is more inclined towards observations with more significant weightage.

Frequently Asked Questions (FAQs)

1. What is the Weighted Average in Google Sheets?

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 Google sheets, the weighted average is usually calculated using the SUMPRODUCT and SUM functions or the AVERAGE.WEIGHTED function.

2. Where is the weighted average used?

The weighted average is used in the following scenarios.

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

3. What are the key differences between average and weighted average in Google Sheets?

In average vs. weighted average, the average is the sum of all values divided by their total count. However, the weighted average in Google Sheets multiplies all the numbers by their weights and is divided by the sum of the weights.

The average is used when simple data sets with no weightage are involved. However, weighted Average is widely used in financial analysis as a more accurate method when complex data sets are involved.

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

Download Template

This article helps understand the Weighted Average in Google Sheets features through examples. You can download the template and use it instantly.

Guide to What is the Weighted Average in Google Sheets. We explain how to calculate the weighted average for values with detailed examples and points to note. You can learn more from the following articles –

Types of Charts in Google Sheets

PV in Google Sheets

NPER Function in Google Sheets

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *