XcelLearner
Board Regular
- Joined
- Feb 6, 2016
- Messages
- 52
- 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??