Calculating Weighted Average in Excel (Using Formulas)

Bonus question!

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.

Continue

/en/excel-tips/concatenate-excels-duct-tape/content/

Weighted average dates based on weighted average loan size?

Hi all

Hopefully someone can help. I have data which has performance figures for certain periods of time. I basically need to get a weighted performance figure for each discrete annual period over the past 5 years i.e. 01/04/20?? — 31/03/20?? from these figures.

The performance figures vary across different dates so I need to be able to get a working formula to be able to update this every month. I’ve attached a spreadsheet to hopefully make more sense?

Thanks

Converting number to fixed length field

10/18/2019

(8 responses) I need to convert a number, which will be of indeterminate length, into a text string of fixed length. The length is 14 characters long. To f…

Hi —

I assume you want to use SUMPRODUCT because some of your referenced cells may be in separate files? SUMPRODUCT is the only function that can read closed files. Otherwise AVERAGEIFS would probably be the more efficient function. But, the SUMPRODUCT solution looks like this:

=IFERROR(SUMPRODUCT((Raw!\$J\$2:\$J\$2185=calculate!A4)*(Raw!\$C\$2:\$C\$2185{amp}gt;=calculate!\$B\$1)*(Raw!\$C\$2:\$C\$2185{amp}lt;=calculate!\$C\$1)*(Raw!\$I\$2:\$I\$2185))/SUMPRODUCT((Raw!\$J\$2:\$J\$2185=calculate!A4)*(Raw!\$C\$2:\$C\$2185{amp}gt;=calculate!\$B\$1)*(Raw!\$C\$2:\$C\$2185{amp}lt;=calculate!\$C\$1)),»»)

First it checks the Advisor name, the next two tests are to collect anything greater than or equal to the start date, and anything less than or equal to the end date. Finally it multiplies the score of each match. The SUMPRODUCT in the denominator is the exact same formula except it doesn’t multiply by the score — it’s just a count of the number of matches. I noticed for different date ranges that some advisors don’t have any data, which will generate an error. That’s why I enclosed the whole formula within an IFERROR function that returns «» if there are not matches. «» is ignored in by your AVERAGE formula in cell B15. So if some advisors have no data, you will still get an average of the advisors that do.

I have attached your file with this formula in the table.

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.

writing it all in one formula we could use for instance:

Formula:

But note that we often refer to DATE(2018,6,1)) and to EOMONTH(DATE(2018,6,1),0)) so having this dates written into some cells we couild end up with shorter formula

PS. to attach a sample workbook :
Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you’re trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi all,

A B C
2 1/06/12 200 5
3 5/06/12 300 6
4 10/06/12 600 4

I have a data set representative of the above (although thousands of rows) and need to calculate a weighted average grade for those values which have a date {amp}gt;= 1/06/12 and {amp}lt;= 6/06/12. Any thoughts on how I can do this?

Use SUMPRODUCT to calculate weighted averages

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.

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:

=PROPER(A2)

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.

Continue

/en/excel-tips/how-to-count-cells-with-counta/content/

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:

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.

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!

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:

=SUM(B2*C2,B3*C3,B4*C4,B5*C5,B6*C6,B7*C7,B8*C8)

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:

=SUM(B2:B8*C2:C8)

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:

Combining names

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.

Continue

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.

Counting invitees

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:

=COUNTA(A2:A100)

Понравилась статья? Поделиться с друзьями: