Don’t forget to share this post

The mean, median, and mode are three measures of a dataset’s central tendency. The arithmetic mean or average is a very popular measure and simple to calculate: sum the values and divide by the number of values. However, the calculation is based on the assumption that all items in the set contribute equally, and this isn’t always the case. If the items in your dataset have different weights - some are worth more than others - you should calculate the weighted average instead.

In this guide, you will learn about weighted averages and how they differ from simple averages, as well as the formula used to calculate a weighted average. You have step-by-step instructions on how to calculate weighted averages manually and how to do it in Google Sheets. Finally, you have two examples of financial uses for this measure: Weighted Average Shares Outstanding and Weighted Average Cost of Capital (WACC).

What is a Weighted Average?

A weighted average considers the relative contribution of each item in the set. This makes it ideal in situations where you can quantify these contributions or weights. For example, course grades are usually composed of different assessments, like exams and projects. These assessments have different weighting, so a simple average is not helpful.

How to Calculate Weighted Averages?

Calculating the weighted average of a set of values is simple and very similar to calculating a simple average. First, multiply each item by its relative weight. Second, add up the results of the previous step. Third, divide the total by the sum of the weights.

weighted average = (value1*weight1 + value2*weight2 +...valuen+weightn) / (weight1+weight2+...weightn)

For example, imagine that you’ve just finished an online course with three graded assessments with different weights: quiz (10% or 0.1), project (40% or 0.4), and exam (50% or 0.5). You already know your grade on each assessment, so you can easily calculate your final grade.

  • Quiz Grade: 69
  • Project Grade: 95
  • Exam Grade: 75

Weighted average = Final grade = ((69*10)+(95*40)+(75*50))/(10+40+50) = (690 + 3800 + 3750)/(100) = 8240/100 = 82.4

However, you can simplify the calculation by expressing the weights as decimals so that they add up to 1.

Final grade = (69*0.1)+(95*0.4)+(75*0.5) = 6.9 + 38 + 37.5 = 82.4

How to Calculate Weighted Averages in Google Sheets?

Now that you know how to calculate weighted averages manually, let’s see how much easier it is to do using Google Sheets. I will use the same example as in the previous section for comparability, but in this case, I’ll calculate the final grades for a class of 10. First, by using the formula shown above, then by using a built-in function in Google Sheets: AVERAGE.WEIGHTED.

Custom Table and Weighted Average Formula

Follow the steps below to find the final grades using the weighted average formula.

  1. 1. Open Google Sheets.
How to Calculate Weighted Averages Examples Open Google Sheets
How to Calculate Weighted Averages (+ Examples) - Open Google Sheets
  1. 2. Set up the data as shown below.
How to Calculate Weighted Averages Examples Set Up Data
How to Calculate Weighted Averages (+ Examples) - Set Up Data
  1. 3. In the cell where you want the first student’s final grade, type in the equal sign and opening parenthesis.
How to Calculate Weighted Averages Examples Add Formula
How to Calculate Weighted Averages (+ Examples) - Add Formula
  1. 4. Select the quiz grade and multiply by the quiz weight. Use absolute referencing for the weight.
How to Calculate Weighted Averages Examples First Item Weight
How to Calculate Weighted Averages (+ Examples) - First Item & Weight
  1. 5. Type the plus sign, then select the project grade and multiply by the project weight. Use absolute referencing for the weight.
How to Calculate Weighted Averages Examples Second Item Weight
How to Calculate Weighted Averages (+ Examples) - Second Item & Weight
  1. 6. Type another plus sign, then select the exam grade and multiply by the exam weight. Use absolute referencing for the weight.
How to Calculate Weighted Averages Examples Third Item Weight
How to Calculate Weighted Averages (+ Examples) - Third Item & Weight
  1. 7. Close the parenthesis and divide by the sum of the weights. Press ‘Enter’ to see the result.
How to Calculate Weighted Averages Examples Divide by Sum of Weights
How to Calculate Weighted Averages (+ Examples) - Divide by Sum of Weights
  1. 8. To get the rest of the grades, grab the fill handle and drag it down to the last row.
How to Calculate Weighted Averages Examples Weighted Averages
How to Calculate Weighted Averages (+ Examples) - Weighted Averages
Top Free Google Sheets Templates and Financial Models
Top Free Google Sheets Templates for 2023

Top Free Google Sheets Templates and Financial Statements to help you manage your business financials, monitor performance, and make informed decisions.

READ MORE

AVERAGE.WEIGHTED Function

Follow the steps below to find the final grades, but this time, use Google Sheets’ built-in function: AVERAGE.WEIGHTED.

  1. 1. Open Google Sheets to the spreadsheet with your data.
How to Calculate Weighted Averages Examples Open Google Sheets 2
How to Calculate Weighted Averages (+ Examples) - Open Google Sheets
  1. 2. In the cell where you want the weighted average, type the formula AVERAGE.WEIGHTED.
How to Calculate Weighted Averages Examples AVERAGE WEIGHTED
How to Calculate Weighted Averages (+ Examples) - AVERAGE.WEIGHTED
  1. 3. For the first parameter, select the quiz grade. After the comma, select the quiz weight using absolute referencing.
How to Calculate Weighted Averages Examples First Item Weight 2
How to Calculate Weighted Averages (+ Examples) - First Item & Weight
  1. 4. Next, add the project grade and the project weight, using absolute referencing for the last.
How to Calculate Weighted Averages Examples Second Item Weight 2
How to Calculate Weighted Averages (+ Examples) - Second Item & Weight
  1. 5. Finally, add the exam grade and use absolute referencing for the weight. Close the parenthesis and press ‘Enter’ to see the result.
How to Calculate Weighted Averages Examples Third Item Weight 2
How to Calculate Weighted Averages (+ Examples) - Third Item & Weight
  1. 6. Grab the fill handle and drag it down to the last row to get the rest of the grades.
How to Calculate Weighted Averages Examples Weighted Averages 2
How to Calculate Weighted Averages (+ Examples) - Weighted Averages
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Weighted Average Examples

Below, you have two examples of how weighted averages are used in financial calculations.

Weighted Average Shares Outstanding

Imagine CompanyX has reported earnings of $300,000 at the end of the year. To calculate earnings per share, you need to divide by the number of shares outstanding. However, what if new shares were issued during the year? CompanyX had 150,000 shares outstanding until it issued 50,000 shares in the second quarter. Which figure should you use for outstanding shares? 150,000 or 200,000? Instead, you calculate the weighted average based on the proportional amount of time covered. In other words, 150,000 accounts for the first quarter or 0.25 of the whole year, while 200,000 accounts for the other three quarters (0.75).

  1. 1. Open Google Sheets and set up the data as shown below.
How to Calculate Weighted Averages Examples Open Google Sheets 3
How to Calculate Weighted Averages (+ Examples) - Open Google Sheets
  1. 2. In an empty cell, type the AVERAGE.WEIGHTED function.
How to Calculate Weighted Averages Examples AVERAGE WEIGHTED 2
How to Calculate Weighted Averages (+ Examples) - AVERAGE.WEIGHTED
  1. 3. Select the first quantity, followed by a comma, then the first weight, followed by another comma.
How to Calculate Weighted Averages Examples First Quantity Weight
How to Calculate Weighted Averages (+ Examples) - First Quantity & Weight
  1. 4. Select the second quantity, add another comma, and select the second weight. Close the parenthesis and press ‘Enter’ to see the result.
How to Calculate Weighted Averages Examples Second Quantity Weight
How to Calculate Weighted Averages (+ Examples) - Second Quantity & Weight
  1. 5. That’s it. The weighted average number of shares outstanding is equal to 187,500.
How to Calculate Weighted Averages Examples Weighted Average Number of Shares Outstanding
How to Calculate Weighted Averages (+ Examples) - Weighted Average Number of Shares Outstanding
  1. 6. To find the Earnings Per Share (EPS), divided the earnings by 187,500.
How to Calculate Weighted Averages Examples Earnings Per Share
How to Calculate Weighted Averages (+ Examples) - Earnings Per Share

Weighted Average Cost of Capital (WACC)

The cost of capital refers to the minimum return a company must earn before it starts generating value. The business needs to generate enough income to cover the cost of the capital it uses to fund its operations, considering both debt and equity.

The following formula is used to calculate the weighted average cost of capital.

WACC = ((equity / (equity + debt)) * required rate of return) + ((debt / (equity + debt)) * cost of debt) * (1 - tax rate)

The formula considers the relative weight of debt and equity and applies the respective cost of each to get an average and weighted value of the cost of capital.

  1. 1. Open Google Sheets and set up the data as shown below.
How to Calculate Weighted Averages Examples Set Up Data 2
How to Calculate Weighted Averages (+ Examples) - Set Up Data
  1. 2. In an empty cell, type in the WACC formula shown above by referencing the corresponding cells.
How to Calculate Weighted Averages Examples Add WACC Formula
How to Calculate Weighted Averages (+ Examples) - Add WACC Formula
  1. 3. Press ‘Enter’ to see the result.
How to Calculate Weighted Averages Examples WACC Value
How to Calculate Weighted Averages (+ Examples) - WACC Value

Conclusion

Weighted averages are useful in many contexts. Unlike simple averages, they consider the relative importance or weight of each item to get a weighted average. This measure is frequently used in many fields, including education, sports, retail, and of course, finance.

You now know what a weighted average is, the formula used to calculate it, and how it differs from a simple average. You know how to calculate a weighted average manually and how to calculate it using Google Sheets. Finally, you’ve seen examples of two financial metrics based on weighted averages: Weighted Average Shares Outstanding and Weighted Average Cost of Capital (WACC).

To learn more about the cost of debt, equity, and capital, check out these guides:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Jan 30 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets