Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct array
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct array
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... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Jumbo Jock" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct array
In article ,
Jumbo Jock wrote: 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. -- Domenic http://www.xl-central.com |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct array
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct array
In article ,
Domenic wrote: In article , Jumbo Jock wrote: 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. 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) -- Domenic http://www.xl-central.com |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct array
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 |
#7
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Possible Array or Sumproduct Formula | Excel Worksheet Functions | |||
Array or SumProduct or other? | Excel Worksheet Functions | |||
Which is faster sum(if) as an array or sumproduct? | New Users to Excel | |||
Sumproduct Excluding Array | Excel Worksheet Functions | |||
SUMPRODUCT - How can I use does not equal in an array? | Excel Worksheet Functions |