First260
My feedback
-
1 vote0 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →First260
shared this idea
·
-
821 votes142 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
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 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
·
-
20 votes3 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
40 votes10 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
2 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
270 votes38 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
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 -
11 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
·
-
88 votes4 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →First260
supported this idea
·
-
18 votes5 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
·
-
18 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
-
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
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 CSEMy 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
·
-
28 votesFirst260
supported this idea
·
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