Hi all, hope things are going well.
I am trying to calculate a weighted average using sumproduct but have a little problem: I can't just use normal ranges in the sumproduct and sum formulas.
Assume row 1 has my values (in yellow) and row 2 has my weights (in green), then normally I would calculate the weighted average as in cell F1. But I have my values and weights in one row, alternating, like in row 4. So when defining my ranges for the sumproduct function and for the sum function, I need to somehow do it such that each range consists only of every other cell in that row, accounting also for another column that always comes in between.
I have hunch that one might put in a condition for each range (or array?) that it consist of the 2nd of every three columns for values, and the 3rd of every three columns for weights, but I'm not sure how to execute that.
Thanks for any help!!
I am trying to calculate a weighted average using sumproduct but have a little problem: I can't just use normal ranges in the sumproduct and sum formulas.
Assume row 1 has my values (in yellow) and row 2 has my weights (in green), then normally I would calculate the weighted average as in cell F1. But I have my values and weights in one row, alternating, like in row 4. So when defining my ranges for the sumproduct function and for the sum function, I need to somehow do it such that each range consists only of every other cell in that row, accounting also for another column that always comes in between.
I have hunch that one might put in a condition for each range (or array?) that it consist of the 2nd of every three columns for values, and the 3rd of every three columns for weights, but I'm not sure how to execute that.
Thanks for any help!!