Sumproduct array

[Jumbo Jock]

Hi there,
I have a vertical data array that begins in cell C5 and ends at cell C214.
Each pair of consecutive lines contains data applicable to a single country
and I need to be able to calculate a global weighted average in cell C215. So
the data array looks like this:-

Country A data 95.4
Country A number 1026
Country B data 101.2
Country B number 504
Country C data 100.9
Country C number 14526
etc
etc

I want to use the sumproduct formula but can't seem to make it work. There
must be a quicker way than creating a formula that multiplies each pair of
lines together and divides the sum by the sum of the "number" lines but I
can't figure it out? Anyone out there help?
Thanks

 

[Sheeloo]

Assumig D1 has country number 1026 this will give you the sum of the values
for 1026
=SUMPRODUCT(--($C$6:$C$214=D1),$C$5:$C$213)
assuming your data is in A1:A101 with country numbers in A1, A4, A6,..A102
and this will give you the count of values for country number 1026
=SUMPRODUCT(--($C$5:$C$213=D1))

You can copy this down wi th other country numbers in D2,D3... and then get
the weighted average based on values in the two columns with the above
formula...

Warning: This will not work if the country number matches any of the values...

 

[Domenic]

Hi there,
I have a vertical data array that begins in cell C5 and ends at cell C214.
Each pair of consecutive lines contains data applicable to a single country
and I need to be able to calculate a global weighted average in cell C215. So
the data array looks like this:-

Country A data 95.4
Country A number 1026
Country B data 101.2
Country B number 504
Country C data 100.9
Country C number 14526
etc
etc

I want to use the sumproduct formula but can't seem to make it work. There
must be a quicker way than creating a formula that multiplies each pair of
lines together and divides the sum by the sum of the "number" lines but I
can't figure it out? Anyone out there help?
Thanks

Is this what you mean?

=AVERAGE(SUBTOTAL(9,OFFSET(C5:C214,ROW(INDIRECT("1:"&ROWS(C5:C214)/2))*2-
2,0,2)))

....confirmed with CONTROL+SHIFT+ENTER.

 

[Ashish Mathur]

Hi,

You may try this:

=SUMPRODUCT(1*(MOD(ROW(E5:E10),2)=1),E5:E10*E6:E11)/SUMPRODUCT(1*(MOD(ROW(E5:E10),2)=0),E5:E10)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Domenic]

Is this what you mean?

=AVERAGE(SUBTOTAL(9,OFFSET(C5:C214,ROW(INDIRECT("1:"&ROWS(C5:C214)/2))*2-
2,0,2)))

...confirmed with CONTROL+SHIFT+ENTER.

Misunderstood... It looks like the solution provided by Ashish Mathur
will provide you with the desired result. However, for robustness, his
formula can be modified as follows...

=SUMPRODUCT(--(MOD(ROW(C5:C213)-ROW(C5),2)=0),C5:C213,C6:C214)/SUMPRODUCT
(--(MOD(ROW(C5:C213)-ROW(C5),2)=0),C6:C214)

 

[Jumbo Jock]

To Ashish and Domenic,
Absolutely brilliant - exactly what i needed. I shall re-use this formula in
many applications I am sure.

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com