Question Locked

Getting the Weighted Average of Every Other Column, excluding blank cells

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi!

I'm totally lost and I hope you can help me out...
I need to get the Weighted Average of every other filled out column.

What formula can I use in Excel?

2 replies

by thumbsup2

If, by Weighted Average, you're asking something like getting the average price paid for a unit across three purchases, where each purchase is for a different number of units at a different price per unit, you would use the SUMPRODUCT formula.

For example, if you had the cost per unit in column A and the total number of units purchased in column B as follows:

20 500
25 750
35 200

... the formula would look like this:

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

It divides the total cost of all three orders by the total number of units ordered (24.66)

----------

In your example problem, you will need to decide which column is weighted and change the cell references to point to the correct columns.

Hope this helps.

This discussion is closed to new replies.