2 Functions to Calculate Weighted Averages in Your Excel

Sometimes you will calculate the weighted averages for your data. Here you can use two functions to calculate it quickly.

In Excel, you will certainly do many calculations. Among those calculations, you will sometimes need to calculate the weighted average. For example, there are sales volumes of different products. The image below shows the sales volume of one representative. Here you need to calculate the weighted average of different sales representatives. And the weight of different product is also different.An Example for Weighted Average

Instead of calculating the weighted average manually, you can also use two different functions to finish your work.

SUM Function

  1. Click a blank cell in the worksheet. Here we click D2 in the worksheet.
  2. And then input the formula into the cell:

=SUM(B2*C2+B3*C3+B4*C4)/SUM(C2:C4)

This formula will calculate the numbers one by one.

  1. Next press the button “Enter” on the keyboard. Thus, you will get the result immediately in the cell.Result of SUM

And you can continue using this function to calculate the weighted average for other sales representatives. But in this formula, you still need to input cell reference one by one. Therefore, you may also try the second method.

SUNPRODUCT Function

  1. Click a blank cell in the worksheet. Here we click cell D3.
  2. And now input the formula into the cell:

=SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

The SUMPRODUCT function will multiply the numbers in the two arrays and then sum up those products. You may change the two arrays according to the actual range in the worksheet.

  1. Next, press the button “Enter” on the keyboard. And then you can check the result in the cell.Result of SUMPRODUCT

This formula will produce the same result. You can see that using the SUMPRODUCT function, you don’t need to input all the cells into the formula. And this method can be quicker that using SUM function. You can also apply it in your actual worksheet.

Possible Causes for Excel Crash

There are many different reasons that can cause damage to your Excel files. Human errors, virus, malware, natural disasters and other reasons can all do harm to your files. Some of the reasons cannot be avoid. Therefore, once Excel corruption happens, you will suffer a lot. At this moment, you can use out powerful tool to repair Excel. This tool is able to repair xls file damage and dealing with other Excel crashes. Even if your file is severely damaged, you can use this tool to recover it. Almost all the essential data and information can be retrieved.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx document and outlook repair software products. For more information visit www.datanumen.com

Comments are closed.