What I would like to see in Excel 2016

[Peter_SSs]

-The ability to resize the 'Evaluate Formula' window.

I'd definitely vote for that one!

 

[Peter_SSs]

Improvement to the INDEX function so you could get it to (easily) return a null string instead of a zero if the target cell is empty.
So we don't have to do constructs like
=IF(INDEX(A1:A6,3)="","",INDEX(A1:A6,3))

 

[shift-del]

Hello Peter

Improvement to the INDEX function so you could get it to (easily) return a null string instead of a zero if the target cell is empty.
So we don't have to do constructs like
=IF(INDEX(A1:A6,3)="","",INDEX(A1:A6,3))

But that applies to every lookup function.
So it should be a more general approach like: IFEMPTY() or IFNULL().

 

[Peter_SSs]

Hello Peter



But that applies to every lookup function.
So it should be a more general approach like: IFEMPTY() or IFNULL().

True. Would be useful even for something as simple as =A1

 

[DocAElstein]

True. Would be useful even for something as simple as =A1

…a bit along these lines . Some way of truly getting a cell or range to be seen by Excel as it was as if nothing had ever been done to it. This seems to be a fundamental problem with Excel not being truly/ fully able to „forget“!. (Maybe deleting comes close sometimes). Even as a beginner I see lots of inconsistent problems finding last cells, empty cells, as well as strange inconsistent memory increases in File size that do not tie up with wot is there, after clearing, deleting etc. So a command like Cells(y,x).Forget would be good

Alan Elston

 

[Fazza]

Obviously VBA can be used to make cells empty.

Also queries do this. There are many possibilities. Along the lines of lookup type situations,

UPDATE table A INNER JOIN another_table B ON A.common_field = B.common_field
SET A,whatever = B.whatever

Using queries instead of INDEX/MATCH removes formulas and works well with nulls.

UPDATE table A INNER JOIN another_table B ON A.common_field = B.common_field
SET A,whatever Null
WHERE some_condition

Many questions & complex formulas are caused by the poor data structures used.

 

[DocAElstein]

Obviously VBA can be used to make cells empty….. Along the lines of lookup type....

Sorry, I am a complete beginner and with Look Up things I have no experience.

…….lots of inconsistent problems finding last cells, empty cells, as well as strange inconsistent memory increases in File size that do not tie up with wot is there, after clearing, deleting etc. So a command like Cells(y,x).Forget would be good..

. Can you tell me please if there IS a way to achieve this, that is to say set a cell or range to exactly as if it was never touched?. It is not such a problem for me now, as I have now , thanks amongst other things to help from this forum (http://www.mrexcel.com/forum/excel-...%3D-y-%96-1-usedrange-rows-count-anomale.html ), just about got up on the different ways of finding the last bit, or end of something. But originally, as many beginners, I was continually confused with wot exactly for example UsedRange was talking about..


. I am continually overwhelmed by what is possible in VBA but at the same time frustrated because of lack of experience in finding out exactly how! – And the Microsoft Help Functions, F1, F2 Libraries, etc. do not seem to be keeping up with things! The help from you experienced users in this Forum is invaluable. - In my day we had command reference books. That does not seem to be possible for Microsoft these days because the possibilities (and resultant Book Weight!) are just too big!? – Clearly no-one person knows everything. – (Some of you guys come closest).
Alan.

P.s. Re: What I would like to see in Excel 2016: Improved Help Functions!

 

[XOR LX]

But that applies to every lookup function.
So it should be a more general approach like: IFEMPTY() or IFNULL().

Indeed! Or even an IFZERO().

I often use IFERROR() in these cases so as not to have to repeat the main clause, especially if that clause is an extremely long one.

Sometimes this is because of a particularly long filepath in the formula (e.g. to a closed workbook), which means that a repetition of the main clause makes even a simple VLOOKUP hard to digest.

But more often it's because I have a formula construction made up of a long series of functions which results in an array from which I'd like to exclude zeroes (or some other value), before passing on to a final function for processing.

Then, rather than repeating the whole main clause again I employ a construction with IFERROR, taking advantage of the fact that only zero will produce an error when reciprocated with unity, along the lines of:

=IFERROR(1/(1/(...

See here for more:

http://excelxor.com/2014/08/29/iferror-techniques-for-excluding-certain-values-from-results/

Regards

 

[Rick Rothstein]

Then, rather than repeating the whole main clause again I employ a construction with IFERROR, taking advantage of the fact that only zero will produce an error when reciprocated with unity, along the lines of:

=IFERROR(1/(1/(...

I like this idea... have you ever encountered any Excel induced rounding issues with the double reciprocal such that X ends up not equaling 1/(1/X)?

 

[XOR LX]

I like this idea... have you ever encountered any Excel induced rounding issues with the double reciprocal such that X ends up not equaling 1/(1/X)?

Thanks. And a good question.

I'm quite surprised myself that I seem to have not yet had the need to add a rounding function when using this construction, especially when using the square/square-root one (or maybe the chance of rounding issues is less with whatever algorithm Excel uses for these calculations?).

I guess to be rigorous I should stop checking each case on a one-by-one basis and accept that it's bound to happen sooner or later (though like I say, it - strangely - still hasn't yet!). In which case I should start wrapping it in some rounding function as default.

Regards