Feedback by UserVoice

First260

My feedback

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

    We’ll send you updates on this idea

    First260 supported this idea  · 
  2. 287 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    First260 commented  · 

    Preferable to avoid OFFSET with hard-coded references and also allow for blanks or text in range like SUM,

    =MMULT(1^TRANSPOSE(ROW(matrix)),N(+matrix))

    Now with LAMBDA one can define like a built-in function without being distracted by implementation details, eg

    =SUMROWS(B2:D3)

    An error occurred while saving the comment
    First260 commented  · 

    This is no longer an issue, just need to insert previous formulas inside LAMBDA in the name definition:

    SUMCOLS =LAMBDA(x,MMULT(SEQUENCE(1,ROWS(x),1,0),x))

    SUMROWS =LAMBDA(x,MMULT(x,SEQUENCE(COLUMNS(x),1,1,0)))

    An error occurred while saving the comment
    First260 commented  · 

    I also like the suggestion to add BYROW / BYCOL functions that would return arrays of row or column ranges. Then for an array of column sums one could use:
    =SUMIF(BYCOL(Table1),"<>")

    An error occurred while saving the comment
    First260 commented  · 

    I'd suggest generalising the concept by adding a dimension argument to aggregation functions (like in numpy / matlab). The dimension argument would refer to rows (1), columns (2) or areas (3) for multi-area or 3D ranges. Examples might include
    =XSUM(Table,1) returns an array of row sums
    =XMIN(Table,2) returns an array of column minima
    =XAGGREGATE(1,,3,Tables) returns a 2d array aggegating tables over different areas or sheets

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

    We’ll send you updates on this idea

    8 comments  ·  Excel for the web » Formulas and Functions  ·  Flag idea as inappropriate…  ·  Admin →
    First260 supported this idea  · 
    An error occurred while saving the comment
    First260 commented  · 

    With the introduction of LET and now LAMBDA this becomes much more important.

    Similar to modern IDEs it'd be ideal to have a detachable formula bar that could be dockable like the Editor for Office Scripts. Improved formatting and debugging tools should soon follow with any luck

  4. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    First260 shared this idea  · 
  5. 831 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for your support for this suggestion. As some of the comments have concluded, there are a few complex technical issues and considerations that have delayed this from being implemented.

    We do think that this suggestion has merit but we don’t think that we’ll be able to devote time to it in the near future.

    One of the primary reasons for this request was to have a better way to handle formula cells in line charts, and this can be accomplished using the option to show #N/A as an empty cell. You can use the NA() function in your formulas as appropriate, and choose the option to “Show cells with #N/A as an empty cell”. To find the option for your chart, go to the Select Data dialog for the chart, and click the Hidden and Empty Cells button.

    We know there are other scenarios where a proper NULL()…

    An error occurred while saving the comment
    First260 commented  · 

    Indeed, well from having worked closely with the C API, i have already tried to outline in previous comments why no function can return a true NULL currently and why more fundamental changes are necessary.

    "Painting is very easy when you don't know how, but very difficult when you do" ~ Edgar Degas

    An error occurred while saving the comment
    First260 commented  · 

    An update for charting was provided 3 years back (see link further down.)

    For formulas the goal should be to allow null results in cells like Google sheets does (ie a null data type). With the recent updates to the calc engine, now seems like a good time if this is ever going to happen.

    An error occurred while saving the comment
    First260 commented  · 

    @Roy Maybe the opportunity to implement a blank return has finally arrived with the new Dynamic Array evaluation. Since an array containing blanks could only be returned to the sheet in Office365 it should not cause major backward compatibility issues.

    For example both SORT(,) and UNIQUE(,) return {TRUE} when inserted into ISBLANK(.) and could therefore return a blank value to the sheet.

    An error occurred while saving the comment
    First260 commented  · 

    For the record, MSFT did release an update in Sep 2017 that enabled #N/A to be treated as an empty cell in charts ( https://techcommunity.microsoft.com/t5/excel-blog/create-a-chart-in-excel-that-recognizes-n-a-or-blank-cells/ba-p/108453 )

    Jon Peltier said the update fulfilled his main interest when raising this request ( https://peltiertech.com/plot-blank-cells-na-in-excel-charts/ )

    The issue with the general request is that blank cells cannot contain formulas by design in Excel and, barring a breaking change to formula calculation, formulas that return blanks always get converted to values (0,"", or FALSE depending on context).

    An error occurred while saving the comment
    First260 commented  · 

    @AC Wilson: There are various links that describe worksheet structure, one I mentioned before, another is here:
    https://docs.microsoft.com/en-us/office/client-developer/excel/excel-worksheet-and-expression-evaluation

    Technically Excel uses a variant structure (or tagged union), xloper, as specified through the C API. Note that by design nulls are not supported as formula results, but instead are converted to numeric zero.

    There was also an update relating to nulls in charts since this request which is likely what the MSFT response is referring to and a blog post by Jon Peltier confirms this covers his main use case.

    An error occurred while saving the comment
    First260 commented  · 

    Ben, for reasons outlined below a 'proper NULL function' is likely to be destined to fail.

    But now with dynamic arrays one can replicate some of the functionality required,
    For example if you want to enter in C1:
    =IF(ISBLANK(A1),NULL(),A1)
    You could instead enter this formula in B1:
    =IF(INDEX({1,1},ISBLANK(A1)),A1)
    Then hide column B
    This ensures columns A and C will always match even where column A contains blanks

    An error occurred while saving the comment
    First260 commented  · 

    Roy, I think you replied to a post that i retracted after some further investigation. It seems the bottom line is Excel team botched implementing Blank/Empty in the first place and there's really no good way out of the mess. In all versions of Excel the following all return TRUE when A1 is blank:

    =A1=FALSE
    =A1=0
    =A1=""
    =A1=IF(1,)
    =A1=NaN()

    But the right hand sides of these equalities are not equal to each other which has the side effect that we have to handle each case separately in formulas. Does it really make sense to add a new value NULL() which is also equal to blank but yet another different value under the hood?

    Note: NaN() should be equivalent to the IEEE special number NaN and can be defined as a VBA udf as:

    Function NaN() As Double
    On Error Resume Next
    NaN = 0 / 0
    End Function

    but actually displays as a number. Also mathematically speaking the equals operator is non-transitive and hence not an equivalence relation due to this definition of blank.

    An error occurred while saving the comment
    First260 commented  · 

    While this would be nice in theory I think a lot of people have missed the fundamental problem that the formula =A1 returns zero if A1 is blank which means there is no consistent / backward compatible way to have a formula return a blank or null.
    Google sheets on the other hand will return empty in this case which opens up that option.

    In effect you'd need one return for null and another for blank/empty which would just add more confusion if it displayed as empty as well.

    An error occurred while saving the comment
    First260 commented  · 

    ... and just seen the recent Office 365 update to "show #n/a as empty cell"

    An error occurred while saving the comment
    First260 commented  · 

    @A.C.Wilson. The basic sheet structure is (see ecma openxml documentation):

    - Cells are either constants or formulas that return values (number, text, logical, error) or else are empty (blank).

    - Formulas are strings of operators, functions, names, values, or other derived data types (array, reference, missing).

    Introducing a new data type (Null) in addition to these eight would very likely break compatility and involve redesign of many existing functions and features.

    Adding an error value such as #MISSING! (#NULL! already exists) is also possible but adds complexity by introducing another null representation in addition to blank / missing.

    Alternatively, other options could be added for how to handle #N/A or zero length string just as you have for blank cells in chart options.

    [For +Infinity (2^1024 in terms of doubles):
    Function inf() As Double
    On Error Resume Next
    inf=1/0
    End Function]

    An error occurred while saving the comment
    First260 commented  · 

    The reason this has not progressed is it's not possible without making fundamental changes to the API.

    You can already return a Null value within a formula using either IF(1,) or a udf that returns Empty. Both return TRUE when passed to ISBLANK(). The corresponding API values are xltypeMissing and xltypeNil neither of which can be returned to a cell as a result.

    A (dirty) workaround is to use a udf that changes the value of another cell via the EVALUATE function but this will complicate the calculation dependencies and recalculation.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    First260 commented  · 
    First260 supported this idea  · 
  7. 5 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    First260 supported this idea  · 
  10. 6 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    First260 commented  · 

    Agree, i often insert a plus sign so as to see the result with the evaluate formula tool.
    For example:
    =+SORT({2;1})
    ->+{1;2}

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

    We’ll send you updates on this idea

    First260 supported this idea  · 
  12. 41 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    First260 supported this idea  · 
  13. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    First260 supported this idea  · 
  14. 12 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    We’ll send you updates on this idea

    First260 supported this idea  · 
  16. 96 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    First260 supported this idea  · 
  17. 17 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    First260 supported this idea  · 
  18. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    First260 commented  · 

    As mentioned in a blog comment, i'd really like the ability to toggle on / off formula spilling.

    If spilling were turned off for a cell, the spill operator (#) would return a spilled array (as opposed to a range) and mousing over that cell would show a 'live preview' of the spilled values.

    This option would allow array formulas to be coded as a sequence of instructions similar to power query.

    DAX support would enable the sheet to be used as a programming environment.

    First260 supported this idea  · 
  19. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    First260 shared this idea  · 
  20. 17 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    First260 commented  · 

    @Peter, I'm not seeing it on my build yet but it looks like arrays of ranges are going to be flagged up as #CALC! errors:
    https://support.office.com/en-gb/article/-calc-errors-in-excel-d6ee03c5-daf6-426a-8df5-4b284730ab1b

    Nested arrays are quoted as 'not currently supported' which suggests there may be scope to support them in the future. I've added a related suggestion in the other post to be able to pass arrays in functions like SUMIFS which only accept ranges currently

    An error occurred while saving the comment
    First260 commented  · 

    Nice in principle but wouldn't this need changes to array calculation not just XLOOKUP function? For example INDIRECT and OFFSET can return array of ranges but show up as errors when entered in the grid

← Previous 1

Feedback and Knowledge Base