Feedback by UserVoice

# First260

1. ## LAMBDA functions and recursion -- calling a SELF function?

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
2. ## SUMCOLS and SUMROWS functions for Dynamic Arrays

(thinking…)
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. ## Customisable formula bar size. The ability to scroll within the formula bar

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

Thank you for your continued feedback and responses. We are glad to update that the work for this feature has started.

We’ll share more details as we progress.

Thanks!

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. ## NaN and other special numbers in cells

1 vote
(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 shared this idea  ·
5. ## Give us a proper NULL() worksheet function.

(thinking…)
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. ## Add a spline function to create non-linear interpolations

(thinking…)
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. ## Black-Scholes

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
8. ## Eigenvalues and eigenvectors of a matrix

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
9. ## The function price

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
10. ## Evaluate Formulas based on spilled array need to show evaluation for each row (not just formula row)

(thinking…)
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. ## True Dark Mode

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
12. ## Define user-defined functions from the Name Manager

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
13. ## For SPILL/Dynamic Array functions to truly take up ONE cell

1 vote
(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
15. ## VSTACK and HSTACK

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
16. ## Allow UNIQUE() to operate on a rectangular range, returning all unique values

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
17. ## Create Array that joins two arrays

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 supported this idea  ·
18. ## Native DAX and Mashup functions in Excel worksheets

(thinking…)
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. ## Array support in worksheet functions

(thinking…)
Signed in as (Sign out)

We’ll send you updates on this idea

First260 shared this idea  ·
20. ## XLOOKUP to return an array of ranges

(thinking…)
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