Feedback by UserVoice

First260

My feedback

  1. 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  · 
  2. 821 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Great suggestion – thanks again for taking the time to put it on this site and for the thoughtful followup comments. This is pretty related to some other work we’ve got going and already has a fair number of votes, so we’ll work on getting plans in place now and hope to get started on this soon.

    Thanks,
    John [MS XL]

    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.

  3. 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  · 
  4. 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  · 
  5. 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  · 
  6. 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  · 
  7. 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  · 
  8. 20 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. 40 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. 2 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  · 
  11. 270 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  · 

    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

  12. 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  · 
  13. 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  · 
  14. 88 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. 18 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. 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  · 
  17. 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  · 
  18. 18 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

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

    Another possible approach could be to allow 'arrays as references' within database, conditional aggregation and other function arguments that currently only support ranges so that nested arrays such as INDEX({1,2;3,4},,{1,2}) would be handled in a similar way to arrays of ranges and return spilled arrays as result sets.

    Stepping through the Evaluate Formula tool with ranges or arrays as function arguments suggests a link between argument type and results from passing an INDEX formula of this kind, as summarized below:

    ByVal (SUMPRODUCT, MMULT) array truncation
    ByRef (SUBTOTAL, SUMIFS, DSUM) array of errors
    ByRef/ByVal (SUM, AGGREGATE, IMSUM) multi-cell results only with CSE

    My impression is that it'd be simpler to target the second group of functions and replace the errors with calculated results (eg {4,6}) without any significant change to array calc. That said, if the multi-cell CSE results from the third group of functions could be converted to spilled arrays in a consistent manner to this request all the better.

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

    We’ll send you updates on this idea

    1 comment  ·  Excel for Windows (Desktop Application) » Other  ·  Flag idea as inappropriate…  ·  Admin →
    First260 supported this idea  · 
← Previous 1

Feedback and Knowledge Base