Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem (another one)
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 a 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 :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem (another one)
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem (another one)
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*CQ 12)+(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*1 0))/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 On 25-02-10 02:34, arjen van der wal wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sumproduct problem (another one)
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*CQ 12)+(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*1 0))/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 On 25-02-10 02:34, arjen van der wal wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem using SUMPRODUCT | New Users to Excel | |||
SUMPRODUCT problem | Excel Worksheet Functions | |||
Sumproduct Problem | Excel Worksheet Functions | |||
sumproduct problem | Excel Programming | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) |