Feedback by UserVoice

Peter Bartholomew

My feedback

  1. 12 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew commented  · 

    First260
    Agreed. A non-volatile version that would also produce arrays of arrays that would feed into SUM, AND etc etc. would be the eventual goal. You might also like to comment on an ealier proposal

    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/36151087-aggregate-2d-arrays-by-row-or-by-column-especiall

    By all means put up counter proposals if you have better ideas. My aim was to introduce requirements but the approach is for others to determine.

    Peter Bartholomew shared this idea  · 
  2. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew commented  · 

    The dimension issue is possible using existing worksheet functions
    = ROWS( UNIQUE(list) )
    From VBA, n = rng.Rows.Count works OK.

    Appending additional data below a spilt array could be useful as an inbuilt function and is something Charles Williams MVP addresses in his FXLSpeedTools.

  3. 171 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew commented  · 

    @Kevin Osborn
    "dynamic array function inside a table"

    I think that confuses the nature of a table and an array.
    A table is a list of records, each of which is a similar heterogeneous collection of fields in which data is referenced by keyword.
    On the other hand an array is an ordered list which should be homogeneous and is referenced by index (or a combination of indices).

    That leads me to suggest that a total is not part of the array and may logically be placed anywhere one chooses within the workbook; there is no requirement that it should align with the source data table.

    If there happen to be text headers that you wish to use to identify a column then
    = XLOOKUP( "Column2", Table1[#Headers], Table1 )
    would return the column as a range reference.

    You may not have much use for arrays, as distinct from tables, but others do and I suggest the proposal is worth a vote; some such functionality is essential.

    Peter Bartholomew supported this idea  · 
    Peter Bartholomew commented  · 

    Hi Charles

    I fully support this initiative. I had posted a related idea but I am not too fussed about the implementation provided the functionality is there.

    https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/36151087-aggregate-2d-arrays-by-row-or-by-column-especiall

    My aim is not simply to use DA here and there but to build dynamic solutions from the ground up using nothing but dynamic arrays. At the moment my solutions are full of matrix multiplication (MMULT) to perform aggregations by row and by column and to accumulate arrays in either direction.

    There is no chance of rolling such methods out across the Excel community, so a suite of new functions is essential to the success of DA.

    It is such a relief to move away from manual knife-and-fork selection of cells and rows for calculation (more accurately selection by mouse and keyboard shortcuts) and let the calculation logic speak for itself based upon references to entire arrays and tables.

  4. 15 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew commented  · 

    Don't you think SORT would be somewhat misleading. SHUFFLE would be more descriptive.

    Another way of approaching the problem is
    = SORTBY( Names, RANDARRAY( COUNTA(Names) ) )

  5. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew commented  · 

    Withdrawn

    This has been overtaken by events. Modern dynamic arrays achieve the objective and, in some ways, do it better.

    Peter Bartholomew commented  · 

    If you wish to see an independent assessment of some models build using Names and Array formulas only go to

    https://sites.google.com/site/beyondexcel/project-updates/multi-cellarraymodelling

    Despite the simplicity of the approach, changing the array dimensions to accommodate further time periods (for example) is still a pain and the arrays have to be adjusted one by one. The insertion of additional rows through an array formula is trapped as an error despite there being no ambiguity inherent in the process.

    If one could simply use a resize handle (as is done for Tables), all the tables linked to a given index could be automatically resized, so removing a significant source of error.

    This will never be implemented by popular demand. It is up to Microsoft to show leadership as they did for the implementation of Tables in Excel 2007 or the more recent Get and Transform that is aimed at supporting data analysts, as opposed to modellers.

    Peter Bartholomew shared this idea  · 
  6. 52 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew supported this idea  · 
    Peter Bartholomew commented  · 

    Agreed. Full screen would be good.
    With large arrays it is probable sufficient to see the 'corner' elements once you have stepped out from the array calculation itself.

  7. 4 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew commented  · 

    Now as I have settled in to using dynamic arrays, I find that this limitation affects about 50% of the models I build. The observable symptoms are blocks of 'fill-down' formulas involving @k#
    [k being a defined name applied to the anchor cell of a spilt array].

    Even worse is
    = INDEX( twoDArray#, @k#, 0 )
    [defining a row of the array]
    The functions then applied to such objects might be simple aggregations or functions such as FILTER and UNIQUE.

    The formula needs to be applied for every value of 'k#', hence the fill, but there may be no way of telling in advance how many terms there are within the array. This makes how far to fill a matter of guesswork.

    Peter Bartholomew shared this idea  · 
  8. 9 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew commented  · 

    Fully agree. The ability to create an index array is fundamental to the process of building solutions in Excel using it as one would a program. It exists in Power Query and should exist as an Excel worksheet formula. I could also see myself using it in the form

    = SEQ( 1, ROWS(myArray) )

    assuming the function defaults to give vertical array.
    The simplest form

    = SEQ(5)

    should be interpreted as giving {1;2;3;4;5}

    Peter Bartholomew supported this idea  · 
  9. 4 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew shared this idea  · 
  10. 9 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew supported this idea  · 
  11. 102 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Peter Bartholomew commented  · 

    Full screen would be good. Even then, for a 1000x100 array formula, seeing just the first few rows/columns and the last would be good enough. It is unlikely that I would wish to check all 100,000 values.

    Peter Bartholomew supported this idea  · 

Feedback and Knowledge Base