XcelLearner
New Member
 Joined
 Feb 6, 2016
 Messages
 38
 Office Version

 365
 2016
 Platform

 Windows
Hi,
I have a data set, in which two arrays (in time series) are power generated (C column), and the gas price (M column). I am taking the weighted gas price of the power plant, assumingly by taking SUMPRODUCT/SUM of the arrays (power generated, and gas price). The arrays are created by OFFSET functions.
The issue I am encountering is that although there is no error for each array, SUMPRODUCT gives 0 as an answer.
In more details, for testing purpose in Q4 2019:
 SUM of Q4 2019 power generated gives 1264, the same as result from the formula: =SUM(OFFSET('Monthly Production'!$C$21,(COLUMN(BF1)COLUMN($AU$1))*3,0,3,1))
 SUM of Q4 2019 gas price gives 18.07, the same as from the formula: =SUM(OFFSET('Monthly Production'!$M$21,(COLUMN(BF1)COLUMN($AU$1))*3,0,3,1))
That means that the arrays work as intended.
However, when I SUMPRODUCT of the two arrays, using formula
=SUMPRODUCT(OFFSET('Monthly Production'!$C$21,(COLUMN(BF1)COLUMN($AU$1))*3,0,3,1),
OFFSET('Monthly Production'!$M$21,(COLUMN(BF1)COLUMN($AU$1))*3,0,3,1))
It gives me an answer of zero.
Where did I go wrong, or maybe SUMPPODUCT works in a specific way please??
I have a data set, in which two arrays (in time series) are power generated (C column), and the gas price (M column). I am taking the weighted gas price of the power plant, assumingly by taking SUMPRODUCT/SUM of the arrays (power generated, and gas price). The arrays are created by OFFSET functions.
The issue I am encountering is that although there is no error for each array, SUMPRODUCT gives 0 as an answer.
In more details, for testing purpose in Q4 2019:
 SUM of Q4 2019 power generated gives 1264, the same as result from the formula: =SUM(OFFSET('Monthly Production'!$C$21,(COLUMN(BF1)COLUMN($AU$1))*3,0,3,1))
 SUM of Q4 2019 gas price gives 18.07, the same as from the formula: =SUM(OFFSET('Monthly Production'!$M$21,(COLUMN(BF1)COLUMN($AU$1))*3,0,3,1))
That means that the arrays work as intended.
However, when I SUMPRODUCT of the two arrays, using formula
=SUMPRODUCT(OFFSET('Monthly Production'!$C$21,(COLUMN(BF1)COLUMN($AU$1))*3,0,3,1),
OFFSET('Monthly Production'!$M$21,(COLUMN(BF1)COLUMN($AU$1))*3,0,3,1))
It gives me an answer of zero.
Where did I go wrong, or maybe SUMPPODUCT works in a specific way please??