Weighted avg of numbers not in contiguous row-ie array w lookup?
 

Is there a way to easily get a weighted average of numbers that are not in a
contiguous set of rows or columns? I've tried combining lookup formulas with
arrays, sumif's, and sumproduct's but not succeeded. Used the long way by
summing the products with commas separating columns and clicking each cell,
but there must be an easier way. Thank you,

Weighted avg of numbers not in contiguous row-ie array w lookup?
 

I think you may be able to get there with sumproducts. I'm thinking in
particular about having a column of values and a column of weights, both of
which have rows you don't want to consider (hence the valid data cells are
not contiguous). Add another 'Validity' column indicating whether to include
each row (1) or not (0). Then your weighted average would be
sumproduct(values, weights, validity)/sumproduct(weights,validity).
If your data is even more scattered, the general idea still holds:
(sumproduct(values_range1,weights_range1) +
sumproduct(values_range2,weights_range2) + ... ) /
sum(weights_range1,weights_range2, ...). In this case you each set of ranges
would refer to another set of cells that should be part of the calculation;
in effect you're applying the validity filter in settting up the equation.
--Bruce

"Diane" wrote:

Is there a way to easily get a weighted average of numbers that are not in a
contiguous set of rows or columns? I've tried combining lookup formulas with
arrays, sumif's, and sumproduct's but not succeeded. Used the long way by
summing the products with commas separating columns and clicking each cell,
but there must be an easier way. Thank you,

Weighted avg of numbers not in contiguous row-ie array w looku
 

Thank you! You're awesome!

"bpeltzer" wrote:

I think you may be able to get there with sumproducts. I'm thinking in
particular about having a column of values and a column of weights, both of
which have rows you don't want to consider (hence the valid data cells are
not contiguous). Add another 'Validity' column indicating whether to include
each row (1) or not (0). Then your weighted average would be
sumproduct(values, weights, validity)/sumproduct(weights,validity).
If your data is even more scattered, the general idea still holds:
(sumproduct(values_range1,weights_range1) +
sumproduct(values_range2,weights_range2) + ... ) /
sum(weights_range1,weights_range2, ...). In this case you each set of ranges
would refer to another set of cells that should be part of the calculation;
in effect you're applying the validity filter in settting up the equation.
--Bruce

"Diane" wrote:

Is there a way to easily get a weighted average of numbers that are not in a
contiguous set of rows or columns? I've tried combining lookup formulas with
arrays, sumif's, and sumproduct's but not succeeded. Used the long way by
summing the products with commas separating columns and clicking each cell,
but there must be an easier way. Thank you,