N
Norbert
I have to determine the weighted average of two arrays of cells.
The one array (A) is 7 cells next to each other (in the same row)
The other array (B) is 7 cells underneath each other (in the same column)
On worksheet 1:
The difficult part (for me) is that array (B) is dependend on the week
number, ie. array (B) for week no. 1 are the cells CQ8:CQ14.
For week no.2 the cells are: CQ15:CQ21 and so on.
In cell C8 up to C14, I have the week no. 1 (the number "1" in each cell)
In cell C15 up to C21, I have the week no. 2
And so on.
On worksheet 2:
In column A is the week number.
Next to it, in column D, I want the formula which I am looking for.
Column G to M contain the 7 numbers depicting array (A)
In words, the formula should do this:
Check on worksheet 1, in column C for the same week number as on
worksheet 2 in column A, take the array of cells in column CQ for that
particular week number and calculate the weighted average of found array
of cells with the array of cells in colum G to M (on worksheet 2) and
divide this sum by the sum of array (B)
this is my suggested formula which results in #VALUE!
=SUMPRODUCT((sheet1!C$5:C$370=A12),(sheet1!CQ$5:CQ$370)*(sheet2!G12:M12))/SUMPRODUCT((sheet1!C$5:C$370=A12),(sheet1!CQ$5:CQ$370))
I really hope someone is out there who can follow my explanations
The one array (A) is 7 cells next to each other (in the same row)
The other array (B) is 7 cells underneath each other (in the same column)
On worksheet 1:
The difficult part (for me) is that array (B) is dependend on the week
number, ie. array (B) for week no. 1 are the cells CQ8:CQ14.
For week no.2 the cells are: CQ15:CQ21 and so on.
In cell C8 up to C14, I have the week no. 1 (the number "1" in each cell)
In cell C15 up to C21, I have the week no. 2
And so on.
On worksheet 2:
In column A is the week number.
Next to it, in column D, I want the formula which I am looking for.
Column G to M contain the 7 numbers depicting array (A)
In words, the formula should do this:
Check on worksheet 1, in column C for the same week number as on
worksheet 2 in column A, take the array of cells in column CQ for that
particular week number and calculate the weighted average of found array
of cells with the array of cells in colum G to M (on worksheet 2) and
divide this sum by the sum of array (B)
this is my suggested formula which results in #VALUE!
=SUMPRODUCT((sheet1!C$5:C$370=A12),(sheet1!CQ$5:CQ$370)*(sheet2!G12:M12))/SUMPRODUCT((sheet1!C$5:C$370=A12),(sheet1!CQ$5:CQ$370))
I really hope someone is out there who can follow my explanations