First260
My feedback
-
3 votes1 comment · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
287 votes43 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment 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 -
36 votes
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
-
1 vote0 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →First260
shared this idea
·
-
831 votes133 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
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-evaluationTechnically 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 blanksAn 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 Functionbut 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.
-
7 votes2 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment First260
commented
Having a function that matches the smoothed line chart option would also be very useful. (https://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-does-excel-plot-smooth-curves/c751e8ff-9f99-4ac7-a74a-fba41ac80300)
First260
supported this idea
·
-
5 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
11 votes3 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
11 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
Evaluate Formulas based on spilled array need to show evaluation for each row (not just formula row)
6 votes1 comment · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →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
·
-
118 votes21 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
41 votes12 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
1 vote0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
12 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
4 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
96 votes4 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
17 votes4 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
3 votes2 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
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
·
-
3 votes1 comment · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
shared this idea
·
-
17 votes6 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
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-4b284730ab1bNested 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
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)