sumproduct with custom ranges

[timjohnny]

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!!

 

[earthworm]

try offset function

 

[timjohnny]

Hi earthworm, thanks for the response. I haven't used it before but I'll give it a go now!

 

[Phuoc]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4			2.608696
2	10	11	12	13			2.608696
3
4		1	10	30	2	11	25	3	12	40	4	13
5
Sheet1

Cell Formulas
Range	Formula
G1	G1	=SUMPRODUCT(A1:D1,A2:D2)/SUM(A2:D2)
G2	G2	=SUMPRODUCT(B4:K4*C4:L4*(MOD(COLUMN(B4:K4),3)=2))/SUMPRODUCT(C4:L4*(MOD(COLUMN(C4:L4),3)=0))

 

[timjohnny]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4			2.608696
2	10	11	12	13			2.608696
3
4		1	10	30	2	11	25	3	12	40	4	13
5
Sheet1

Cell Formulas
Range	Formula
G1	G1	=SUMPRODUCT(A1:D1,A2:D2)/SUM(A2:D2)
G2	G2	=SUMPRODUCT(B4:K4*C4:L4*(MOD(COLUMN(B4:K4),3)=2))/SUMPRODUCT(C4:L4*(MOD(COLUMN(C4:L4),3)=0))

Hi Phuoc, thanks a lot for the reply. I've tried this formula, but unfortunately it returns #VALUE!. Within the range I'm looking at, I'll have some cells containing "-". Could that be the issue? And if so, do you have any solution in mind?
Thanks again!

 

[timjohnny]

If MOD can't handle "-" (the main reason I originally considered sumproduct was that it seems to be able to handle "-"), then I have come to think that maybe there's a way to solve this with a Macro using Step? Assuming I have 2401 columns, then maybe it could be something using
for
j = 2401 To 7 Step -3
and
k = 2400 To 7 Step -3,
Sumproduct(5:j,5:k)/sum(5:j)
?

This probably makes no sense, but I thought there might be something in it maybe?