Aug 07 2020 05:12 PM
Hi,
I want to use the Row() function in conjunction with the Offset() function and then both in SumProduct() and I've found it doesn't work. For example, if I use this:
=SUMPRODUCT(OFFSET(Filtered!$A$2,28,8,1,1),--(OFFSET(Filtered!$A$2,28,8,1,1) <0))
I get a valid result. However, if I substitute columns=8 for Row()+5 it I get #VALUE! in the cell.
=SUMPRODUCT(OFFSET(Filtered!$A$2,28,ROW()+5,1,1),--(OFFSET(Filtered!$A$2,28,ROW()+5,1,1) <0))
Am I to conclude that Excel doesn't let you use the Row() function in the Offset() function?
Aug 07 2020 08:40 PM - edited Aug 07 2020 08:49 PM
SolutionIt appears ROW is returning a single element array. There is an old thread here that discusses it.
https://answers.microsoft.com/en-us/msoffice/forum/all/row-function-returning-array-though-it-should...
It looks like a workaround is to wrap ROW with the SUM function to get the array to a single value.
=SUMPRODUCT(OFFSET(Filtered!$A$2,28,SUM(ROW())+5,1,1),--(OFFSET(Filtered!$A$2,28,SUM(ROW())+5,1,1) <0))
Or, you could use ROWS:
=SUMPRODUCT(OFFSET(Filtered!$A$2,28,ROWS($A$1:$A3)+5,1,1),--(OFFSET(Filtered!$A$2,28,ROWS($A$1:$A3)+5,1,1) <0))
Aug 08 2020 06:40 AM
@JMB17 ,
Thanks so much for your answer. It solved my problem. However, it is very non-intuitive. In fact, the definition of ROW() from Formula Builder states, "Returns the row number of a reference." It does not say array. Further, the definition of ROWS() states, "Returns the number of rows in a reference or array."
So I concluded the MS goofed here. Row() should ALWAYS return an integer and Rows() should always return an array. Same for Column() and Columns(). Since this is not the case, they are inconsistent.
Rob
Aug 08 2020 07:29 AM
Rob, that's an opposite.
ROWS(range) calculates number of rows in the range and always returns single number.
ROW(range) returns an array with rows numbers within the range which is widely used in Excel formulas to before SEQUENCE() appeared.
And yes, ROW() or ROW(A1) returns an array from only one elements which usually is silently converted to the scalar value, but not always. ROW() is not the only such function.
I agree, support page doesn't explain that properly, perhaps intentionally not to complicate the things.
By the way, why do you need SUMPRODUCT() in your formula if OFFSET returns only one element?
Aug 08 2020 08:00 AM
You are right. Doh! I stand corrected. Thanks.
Re: SumProduct, I'll be using it with a range returned from Offset. Just didn't show it here.
Rob
Aug 08 2020 09:28 AM
If you are on Excel with dynamic arrays, you may use SUM with multiplication of returned arrays, like
=SUM(OFFSET($A$2,28,ROW()+5,1,1)*(OFFSET($A$2,28,ROW()+5,1,1)<0))
it works with single element as well.
Aug 07 2020 08:40 PM - edited Aug 07 2020 08:49 PM
SolutionIt appears ROW is returning a single element array. There is an old thread here that discusses it.
https://answers.microsoft.com/en-us/msoffice/forum/all/row-function-returning-array-though-it-should...
It looks like a workaround is to wrap ROW with the SUM function to get the array to a single value.
=SUMPRODUCT(OFFSET(Filtered!$A$2,28,SUM(ROW())+5,1,1),--(OFFSET(Filtered!$A$2,28,SUM(ROW())+5,1,1) <0))
Or, you could use ROWS:
=SUMPRODUCT(OFFSET(Filtered!$A$2,28,ROWS($A$1:$A3)+5,1,1),--(OFFSET(Filtered!$A$2,28,ROWS($A$1:$A3)+5,1,1) <0))