I am generating an excel based site report. I want to make

We use cookies to improve your experience. By continuing to use this site you consent to the use of cookies as described in our cookie policy, unless you have disabled them.

Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

Go back-and-forth until satisfied

Rate the answer you receive.

By continuing to use this site you consent to the use of cookies on your device as described in our cookie policy unless you have disabled them.

Ask Ameen Khan Your Own Question

Ameen Khan,

Category: Programming

Satisfied Customers: 12062

Experience: Senior IT Support Specialist at Brookfield Asset Management

101830517

Type Your Programming Question Here...

Ameen Khan is online now

I am generating an excel based site report. I want to make

This answer was rated:

★★★★★

Hi, I am generating an excel based site report. I want to make an opverall site % scorewhich would be system weighted (i.e. 15 observations would equal 100% until one or numerous of those observations were negative for which a minus scroe would be deducted. JA: When did you last update Excel? Customer: office updated today JA: What have you tried so far with your Excel? Customer: ive got formulas in that workded until I decdied to give a + score for anything whioch was above good practice (as a boost) JA: Anything else you want the Microsoft Office Expert to know before I connect you? Customer: I dont think so

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.

Example 2. Finding weighted average with SUMPRODUCT function

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:

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:

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!

-------------

Let me know if you have any follow up questions. I am here to help you.

Ameen Khan and 2 other Programming Specialists are ready to help you