Forum Discussion

Viz's avatar
Viz
Brass Contributor
Nov 18, 2022

ByRow function produces two different results depending on whether row is range or array

In a nutshell, I am trying to compute average with a combination of ByRow, Index, and Average. I needed a sequence number array for this algorithm. When I put this sequence number in a range and use ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 19, 2022

PeterBartholomew1 , hope you are happy with new AFE. Had no time to dig it in details, first impression is quite positive.

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 20, 2022
As for you, it is early days. I do not think I have fully mastered the options available on the Names tab. I have been able to do a bit of editing though.
Modules, I am pretty comfortable with.
Grid is amazing. Hopefully, I will soon be able to switch the formula bar off forever!
  • tdarenkov's avatar
    tdarenkov
    Copper Contributor
    Mar 12, 2023

    PeterBartholomew1 

    I have a similar question. Suppose I have an array (A1#) such that: 

    2
    3
    4 

    I need to create a sequence that in the end looks something like this: 

    2
    2
    3
    3
    3
    4
    4
    4
    4

     

    My thought was the use BYROW(A1#,LAMBDA(x,SEQUENCE(1,4,x,0))) so that I get: 

    22N/AN/A
    333N/A
    4444

     

    And then feed that into TOCOL() and use a FILTER() to filter out the "N/A"s. But the function throws a CALC error. 

     

    Any thoughts? 

    • mtarler's avatar
      mtarler
      Silver Contributor
      Mar 12, 2023
      a) this should really be a new thread (and welcome to add link back to this discussion)
      b) if you hover over the error you are probably getting the array of arrays error
      c) a common work around here is to use REDUCE() and 'build' the array inside and a drop to get rid of the initial value. e.g. (note this is untested and just off the cuff as an example)
      =DROP( REDUCE(0, A1#, LAMBDA( p, q, HSTACK( p, SEQUENCE( q, 1, q, 0) ) ) ), 1)
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Mar 12, 2023

        mtarler 

        Hi Matt

        Going straight for VSTACK would avoid the fill of #N/As.

        = DROP(
    REDUCE(0, seq#,
        LAMBDA(p, q, VSTACK(p, SEQUENCE(q, 1, q, 0)))
    ),
  1 )

        In a properly specified system

        = MAP(seq#, LAMBDA(q, SEQUENCE(q, 1, q, 0)))

        would work.  The 'Nested arrays are not supported' #CALC! error is just plain irritating since the overwhelming majority of solutions I work of have arrays of arrays as their solution.  Mind you my requirements have grown since I first encountered the 'array of array failure'.  I no longer would want to settle for a row of column vectors, packed into a 2D array.  Now I want arrays of 2D arrays, just as one could return with H/VSTACK!

         

Resources