sumproduct problem (another one)

[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

 

[arjen van der wal]

I'm not sure why you need to use SumProduct for the formula. If there are
two arrays and there will always be 7 elements in each array you should be
able to just sum the arrays and divide by 14. Something like:

Dim AvgArray As Double
AvgArray = (WorksheetFunction.Sum(Array1) + _
WorksheetFunction.Sum(Array2)) / 14

Then it's just a matter of defining the ranges that the arrays take their
values from.

 

[Norbert]

Hi Arjen,
I need a weighted average, meaning following.
In case array A is cells G12:M12 and array B is cells CQ8:CQ14,
following calculations have to be done
((G12*CQ8)+(H12*CQ9)+(I12*CQ10)+(J12*CQ11)+(K12*CQ12)+(L12*CQ13)+(M12*CQ14))/sum(CQ8:CQ14)

That will give you the weighted average.
Array A represents "machine down-time" in hh:mm, array B represents the
time the machines were in production.
We work maybe from Mon to Thu 24hrs but on Friday and Saturday we do
only work 10hrs.
The downtime might look like that, for Mon - Friday: 200hrs, 190hrs,
188hrs, 189hrs, 32hrs, 52hrs which results in an average of 141.8hrs.
The weighted average though is:
((200*24)+(190*24)+(188*24)+(189*24)+(32*10)+(52*10))/sum(24+24+24+24+10+10)
which results in: 165.9hrs

My actual problem is in defining the arrays, as it is different ones for
every week. At the moment I define them with their actual cell addresses
but this is very awkward as I would have to do it for 52weeks and next
year I have to redefine them again as they would change.

Thanks

 

[Norbert]

Hi Arjen,
I need a weighted average, meaning following.
In case array A is cells G12:M12 and array B is cells CQ8:CQ14,
following calculations have to be done
((G12*CQ8)+(H12*CQ9)+(I12*CQ10)+(J12*CQ11)+(K12*CQ12)+(L12*CQ13)+(M12*CQ14))/sum(CQ8:CQ14)


That will give you the weighted average.
Array A represents "machine down-time" in hh:mm, array B represents the
time the machines were in production.
We work maybe from Mon to Thu 24hrs but on Friday and Saturday we do
only work 10hrs.
The downtime might look like that, for Mon - Saturday: 200hrs, 190hrs,
188hrs, 189hrs, 32hrs, 52hrs which results in an average of 141.8hrs.
The weighted average though is:
((200*24)+(190*24)+(188*24)+(189*24)+(32*10)+(52*10))/sum(24+24+24+24+10+10)

which results in: 165.9hrs

My actual problem is in defining the arrays, as it is different ones for
every week. At the moment I define them with their actual cell addresses
but this is very awkward as I would have to do it for 52weeks and next
year I have to redefine them again as they would change.

Thanks