Forum Discussion
Problem using Row() function in Offset() function
It 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-not/fcce040c-c297-43d9-a02e-a0038b6be5e0?auth=1It 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))
It 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-not/fcce040c-c297-43d9-a02e-a0038b6be5e0?auth=1
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))
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
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?
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
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.
