Forum Discussion
ByRow function produces two different results depending on whether row is range or array
I followed JamesDuley 's link and found a related question from a personal friend of some 20 years standing. In answering his question, I advanced my own understanding of the problem
TEXTSPLIT combined with BYROW returns an unexpected result when using - Microsoft Community
My conclusions were that BYROW is designed to accept a 2D matrix and pass it to the calculation, row at a time. In the special case of the matrix having only a single column, BYROW will pass single values to the calculation, but they will still be
'Type = 64' and will get caught by any (monumentally undesirable) legacy nested array checks. Passing the single value (1x1) array can be made to work if you always reference its value using '@', in order to convert it to a scalar value.
Where BYROW references a Range, things proceed somewhat differently. In normal use BYROW passes row ranges to the calculation engine (COUNTIFS will work with such an object as its criterion range for example) but, in the special case, the row might reduce to a single cell reference.
= TYPE(cell)
will then return a 1 or 2, so the calculation proceeds differently depending on whether the initial BYROW reference is an array or a range object.
PeterBartholomew1 wrote:Where BYROW references a Range, things proceed somewhat differently. In normal use BYROW passes row ranges to the calculation engine (COUNTIFS will work with such an object as its criterion range for example) but, in the special case, the row might reduce to a single cell reference.
= TYPE(cell)
will then return a 1 or 2, so the calculation proceeds differently depending on whether the initial BYROW reference is an array or a range object.
That's an interesting observation. It looks like BYROW internally does INDEX(array, row_num, 0) for each row to pass to the function. INDEX, like this, returns a scalar if, and only if, array is a single column and it's a reference. This is different from the almost equivalent CHOOSEROWS(array, row_num) which always returns an array.
PeterBartholomew1 Oh, I guess I've seen related post from James here on MTC
