Content of article
We now have the number of responses in cell F2 and the total number of invitees in cell F3. It would be great to calculate the percentage of people who have responded. See if you can write a formula in cell F4 that calculates the percentage.
Remember to use cell references; we want the formula to recalculate whenever we update the spreadsheet.
Previous: Use the PROPER Function to Capitalize Names in Excel
Calculate weighted ignore blanks
Calculating weighted average in Excel
To calculate a weighted average in Excel, simply use the SUMPRODUCT and the SUM function.
1. The AVERAGE function below calculates the normal average of three scores.
Suppose your teacher says, «The test counts twice as much as the quiz and the final exam counts three times as much as the quiz».
2. Below you can find the corresponding weights of the scores.
The formula below calculates the weighted average of these scores.
|Weighted Average||=||20 40 40 90 90 90|
3. We can use the SUMPRODUCT function in Excel to calculate the number above the fraction line (370).
Note: the SUMPRODUCT function performs this calculation: (20 * 1) (40 * 2) (90 * 3) = 370.
4. We can use the SUM function in Excel to calculate the number below the fraction line (6).
5. Use the functions at step 3 and step 4 to calculate the weighted average of these scores in Excel.
A weighted average is an average that takes into account the importance, or weight, of each value. A good example would be calculating a student’s final grade based on their performance on a variety of different assignments and tests.
Individual assignments usually don’t count as much towards a final grade as the final exam—things like quizzes, tests, and final exams will all have different weights. The weighted average is calculated as the sum of all of the values multiplied by their weights divided by the sum of all of the weights.
The following example will demonstrate how to use Excel’s SUMPRODUCT and SUM functions to calculate a weighted average.
Weighted average is a kind of arithmetic mean in which some elements of the data set carry more importance than others. In other words, each value to be averaged is assigned a certain weight.
Students’ grades are often calculated using a weighted average, as shown in the following screenshot. A usual average is easily calculated with the Excel AVERAGE function. However, we want the average formula to consider the weight of each activity listed in column C.
In mathematics and statistics, you calculate weighted average by multiplying each value in the set by its weight, then you add up the products and divide the products’ sum by the sum of all weights.
In this example, in order to calculate the weighted average (overall grade), you multiply each grade by the corresponding percentage (converted to a decimal), add up the 5 products together, and divide that number by the sum of 5 weights:
((91*0.1) (65*0.15) (80*0.2) (73*0.25) (68*0.3)) / (0.1 0.15 0.2 0.25 0.3)=73.5
As you see, a normal average grade (75.4) and weighted average (73.5) are different values.
In Microsoft Excel, weighted average is calculated using the same approach but with far less effort because Excel functions will do most of the work for you.
Example 2. Finding weighted average with SUMPRODUCT function
Excel makes it extremely easy to calculate the average of several cells: Just use the AVERAGE function. But what if some of the values have more «weight» than others? For example, in many classes the tests are worth more than the assignments. For these situations, you’ll need to calculate the weighted average.
Although Excel doesn’t have a weighted average function, it does have a function that does most of the work for you: SUMPRODUCT. Even if you’ve never used SUMPRODUCT before, you’ll be able to use it like a pro by the end of this article.
If you’d like to follow along, you can download our example.
When you think about Excel functions, you probably think about performing calculations with numbers. While it’s true that you can use functions to do lots of handy things with numbers in Excel, some functions can help you format text too.
One good example is the PROPER function, which capitalizes the first letter of every word in a cell. If you have cells containing proper nouns, like names or titles, you can use the PROPER function to make sure everything is capitalized correctly. The PROPER function works in Google Sheets too.
For example, let’s say your company wants to give someone a lifetime achievement award. You’ve asked your coworkers to enter their nominations for the award into this spreadsheet:
Unfortunately, you can see that not everyone has been careful to capitalize the first and last names of the people they want to nominate, so the spreadsheet looks messy. You could go through the column and correct the names manually, but using the PROPER function will be faster and easier.
In this example, the names of the nominees are in column A, so we’ll put our formula in column B. In cell B2, we’ll type a formula that tells Excel to capitalize the name in cell A2, which contains the first name on our list. The formula will look like this:
As you may remember from our Simple Formulas lesson in our Excel Formulas tutorial, it’s important to make sure you start any Excel formula with an equals sign. Once you’ve entered the formula, press the Enter key, and cell B2 will display the name from A2 with the correct capitalization: Thomas Lynley.
Now all we have to do is click and drag the fill handle through cell A14, and column B will display all of the names in the list with the correct capitalization:
Great! Now all the names of the award nominees are correctly capitalized in the spreadsheet. There’s one problem, though: We still have the original uncapitalized names in column A. We can’t delete column A because our formula in column B refers to it.
To do this, select cells B2:B14 and click the Copy command (or press Ctrl C on your keyboard). Then right-click the cell where you want to paste the values (C2, for example), then select the Values button from the menu that appears.
Now we have a column that displays the corrected names but that doesn’t depend on a formula or cell reference. This means we can delete our original columns (column A and column B). There we have it: a nice, neat spreadsheet with all the names of the nominees correctly capitalized.
Previous: Use SUMPRODUCT to Calculate Weighted Averages
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range…
- Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns… Prevent Duplicate Cells; Compare Ranges…
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select…
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more…
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments…
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic…
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF…
- More than
300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial.
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by
50%, and reduces hundreds of mouse clicks for you every day!
There could be various scenarios where you need to calculate the weighted average. Below are three different situations where you can use the SUMPRODUCT function to calculate weighted average in Excel
Below are three different situations where you can use the SUMPRODUCT function to calculate weighted average in Excel
While SUMPRODUCT function is the best way to calculate the weighted average in Excel, you can also use the SUM function.
To calculate the weighted average using the SUM function, you need to multiply each element, with its assigned importance in percentage.
Using the same dataset:
Here the formula that will give you the right result:
This method is alright to use when you have a couple of items. But when you have many items and weights, this method could be cumbersome and error-prone. There is shorter and better way of doing this using the SUM function.
Continuing with the same data set, here is the short formula that will give you the weighted average using the SUM function:
The trick while using this formula is to use Control Shift Enter, instead of just using Enter. Since SUM function can not handle arrays, you need to use Control Shift Enter.<img class="size-full wp-image-14396 aligncenter" src="data:
image/svg xml,» alt=»Calculate Weighted Average in Excel — sum array» width=»519″ height=»249″ data-lazy-src=»https://trumpexcel.com/wp-content/uploads/2016/03/Calculate-Weighted-Average-in-Excel-sum-array.png»>
When you hit Control Shift Enter, you would see curly brackets appear automatically at the beginning and the end of the formula (see the formula bar in the above image).
Again, make sure the weights add up to 100%. If it does not, you need to divide the result by the sum of the weights (as shown below, taking the product example):
You May Also Like the Following Excel Tutorials:
Now we can combine the two functions to determine the student’s final grade based on their scores and the weights of each score. Select the cell where the weighted average should go (for us that’s cell D15) and then type the following formula into the function bar.
Press “Enter” after typing the formula to view the weighted average.
And there you have it. It’s a fairly simple example, but it’s a good one for showing how weighted averages work.
If you have basic knowledge of the Excel SUM function, the below formula will hardly require any explanation:
=SUM(B2*C2, B3*C3, B4*C4, B5*C5, B6*C6,)/SUM(C2:C6)
In essence, it performs the same calculation as described above, except that you supply cell references instead of numbers.
As you can see in the screenshot, the formula returns exactly the same result as the calculation we did a moment ago. Notice the difference between the normal average returned by the AVERAGE function (C8) and weighted average (C9).
Although the SUM formula is very straightforward and easy to understand, it is not a viable option if you have a large number of elements to average. In this case, you’d better utilize the SUMPRODUCT function as demonstrated in the next example.
Excel’s SUMPRODUCT function fits perfectly for this task since it is designed to sum products, which is exactly what we need. So, instead of multiplying each value by its weight individually, you supply two arrays in the SUMPRODUCT formula (in this context, an array is a continuous range of cells), and then divide the result by the sum of weights:
=SUMPRODUCT(values_range, weights_range) / SUM(weights_range)
Supposing that the values to average are in cells B2:B6 and weights in cells C2:C6, our Sumproduct Weighted Average formula takes the following shape:
=SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6)
To see the actual values behind an array, select it in the formula bar and press the F9 key. The result will be similar to this:
<img class="imgl2" loading="lazy" src="https://cdn.ablebits.com/_img-blog/weighted-average/weight-average-sumproduct-arrays.
png» alt=»To see the actual values behind an array, select it in the formula bar and press the F9 key.» title=»To see the actual values behind an array, select it in the formula bar and press the F9 key.»>
So, what the SUMPRODUCT function does is multiply the 1st value in array1 by the 1st value in array2 (91*0.1 in this example), then multiply the 2nd value in array1 by the 2nd value in array2 (65*0.
15 in this example), and so on. When all of the multiplications are done, the function adds up the products and returns that sum.
<img class="imgl2" loading="lazy" src="https://cdn.ablebits.com/_img-blog/weighted-average/weight-average-sumproduct-formula.
To make sure that the SUMPRODUCT function yields a correct result, compare it to the SUM formula from the previous example and you will see that the numbers are identical.
When using either the SUM or SUMPRODUCT function to find weight average in Excel, weights do not necessarily have to add up to 100%. Nor do they need to be expressed as percentages. For example, you can make up a priority / importance scale and assign a certain number of points to each item, as demonstrated in the following screenshot:
Well, that’s all about calculating weighted average in Excel. You can download the Weighted Average spreadsheet and try the formulas on your data. In the next tutorial, we are going to have a close look at calculating moving average. I thank you for reading and look forward to seeing you next week!
Let’s say we have a spreadsheet of contact information with last names and first names in separate columns, and we’d like to combine them to get each person’s full name. In the image below, you can see that the first names are in column B and the last names are in column A. Our formula will go in cell E2.
Before we start typing the formula, there’s one important thing you need to know: CONCATENATE will combine exactly what you tell it to combine, and nothing more. If you want punctuation, spaces, or any other details to appear in the cell, you’ll need to tell CONCATENATE to include it.
In this case, we want the names to have a space in between them (so it doesn’t say JosephineCarter), so we’ll need to add an argument that contains a space. This means we’ll need three arguments:
- B2 (first name)
- » « (a space in quotation marks)
- A2 (last name)
Now that we have our arguments, we can type the following formula into cell E2:
=CONCATENATE(B2, » «, A2)
In the current version of Excel, you can use the new CONCAT function instead of CONCATENATE. The two functions work the same way.
Just like any function, the syntax is important. Make sure to start with an equals sign, and separate each argument with a comma. Note: Depending on where you live, you may need to separate the arguments with a semicolon (;) instead of a comma.
That’s it! When you press Enter, it should display the full name: Josephine Carter.
Now you can click and drag the fill handle down through cell E11, and it should display the full name for each person.
If you’d like an extra challenge, try using CONCATENATE to combine the city and state in column F so it looks like the image below.
Combining numbers and text
You can even use CONCATENATE to combine numbers and text. For example, let’s say we’re using Excel to keep track of a store’s inventory. We currently have 25 apples in stock, but 25 and apples are in separate cells. We want to combine them into one cell so that it looks like this:
To do this, we’ll need to combine three things:
- F17 (number in stock)
- » « (space)
- F16 (product name)
Type the following formula into cell E19:
=CONCATENATE(F17, » «, F16)
Let’s say we want it to say We have 25 apples. We’ll just need to add an argument at the beginning that says We have:
=CONCATENATE(«We have«, F17, » «, F16)
If you wanted to, you could add even more arguments to create more complex statements. Just keep in mind that the syntax always needs to be exactly right, or the formula may not work—and it’s easier to make a mistake with a longer formula.
Previous: How to Count Cells with COUNTA
CONCATENATE: Excel’s duct tape
MacGyver used it. The Apollo 13 crew used it. Whenever people are in a bind and need to stick two things together, they reach for the duct tape. But what you may not know is that Excel has a built-in function that does pretty much the same thing: CONCATENATE.
CONCATENATE lets you combine two or more things in one cell—and despite the long name, it’s actually easy to use. It works the same way in all versions of Excel, as well as in other spreadsheet applications like Google Sheets.
If you’d like to follow along, you can download our example spreadsheet. Note: If you’ve never used Excel functions before, you may want to check out our Functions lesson from our Excel Formulas tutorial first to learn the basics.
We can also use COUNTA to calculate the total number of people that were invited. In cell F3, type the following function and press Enter: