Problem using Row() function in Offset() function

[robtcallahan]

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?

[#2 · Accepted solution]

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...

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))

[#3]

@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

[#4]

@robtcallahan 

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? 

[#5]

@Sergei Baklan,

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

[#6]

@robtcallahan 

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.