Posted to microsoft.public.excel.worksheet.functions
|
|
Sumproduct array
You are welcome
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
"Jumbo Jock" wrote in message
...
To Ashish and Domenic,
Absolutely brilliant - exactly what i needed. I shall re-use this formula
in
many applications I am sure.
:-)
"Ashish Mathur" wrote:
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
"Jumbo Jock" wrote in message
...
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
|