This is a comment reply I tried to post to Rick Rothstein and Willy Vanhaelen here
https://excelribbon.tips.net/T010768...ZIP_Codes.html
Possibly some text annoyed the Firewall?
( Also posted here https://www.excelforum.com/developme...ml#post4629001
http://www.excelfox.com/forum/showth...0201#post10201
and in this Text File which is also atttatched https://app.box.com/s/oz2oga0ym6bue2kqf8assy8uq8qdod0e )
_.________________________________________________ _______
Hi Rick,
Thanks very much for popping by. It is really great to hear your “take” on these things. I am continually frustrated that Microsoft, for example, do not give precise information about these things. It appears they are not sure themselves. People like you seem to be the best authorities on this.
To some extent it appears anyone may have an opinion, as no definitive documentation or understanding exists.
I am very glad you seem to think the If({1}, ___ ) is OK: I had been a bit nervous about suggesting it to Willy, as I had not seen it before. But it seemed logical based on all that I had learnt from these sort of things so far.
I have followed your arguments below. You explained very well and I follow your logic and reasoning. Thanks for clarifying a few things.
I came to some general conclusions about what is going on with multi value formula / array analysis and Evaluate. I think I am more or less in agreement with your appraisal. It’s great to hear it. Most people just accept what happens and hack out some of these interesting “coercing” as they often refer to them, as a way to ”get” array analysis to work. Most seem to have no idea how their solutions work.
This is my take, and the reasoning to the
If({1}, ___ )
working as an alternative to the
If(Row(),___ )
( Until recently I only used or had seen the If(Row(), ___ ) or Index( ____ , 0, 0 ) )
I possibly see things slightly the other way around, but probably in end effect the same ...
I was thinking that one of the reasoning’s why CSE is needed is the following:
It seems that single breadth things ( single column or single row arrays or ranges ) , as well as a single range reference works very efficiently due to some mechanism involving an effective help matrix the size of a worksheet which for a single cell reference effectively has that value in the entire cells. For a single breadth range, that matrix is filled in the “other” dimension across or down the effective matrix over the “length” of the range, effectively duplicating the values in the range. This all explains why a single reference like =A1 works very quickly, and also why single breadth things work so quickly, and also explains Implicit intersection which results in a single breadth reference like =A1:A4 or =A1:D1 not giving an error in some cells in a worksheet.
http://www.excelfox.com/forum/showth...on-and-VLookUp
The problem comes with a multi dimensional range reference like =A1:B2. That will error in any cell. What is required is to somehow do some Controlled Shifting of these effective matrices before the doing for each value effectively a single Evaluation on Enter. Maybe that is why they chose the key combination of Ctrl + Shift + Enter.
In a way, the CSE could be thought of “removing” the direct default connection to the worksheet which results in =A1 giving a result in any cell ( except A1 of course ) and = A1:A2 giving a result in some cells.
My argument then would be that Evaluate does not necessarily do array analysis as such, - It simply does not need the CSE to stop it from erroring. Further my argument is that for any =A1:B2, the array analyses does take place , the values are there, but it messes up in a single spreadsheet cell without CSE.
( Evaluate(“=A1:B2”) works to return an Array as by default Excel holds, I believe such references as Range objects, which then have the default Property of .Value applied in many cases to return the single value or values in an Array as appropriate. That can be proved by setting Evaluate(“=A1:B2”) to a Range object variable. It will return the Range object. Equating Evaluate(“=A1:B2”) to a Variant will result in it defaulting to a value or array of values. )
Coming back to Evaluate and multi value ( array ) analysis. I think Evaluate just evaluates.
In the case under consideration, the range is “there” such as A1:A10 is there in Left(A1:A10,5). This will not error in Evaluate, as it does in all but rows 1 to 10 in a spreadsheet. But Left( ) is not programmed to return an Array. Possibly this is what you are saying by “not array aware”.
But, as you suggest if you embed that text string function within another function that is “array aware”, you can “trick” Left into giving all its available values. My argument there is that once embedded into function that is programmed to do array analysis, then in most cases *** , the following happens:
Excel will evaluate as long and as far and in the dimensions of any arrays “available” . In the case of If(Row(), __ ) or If({1}, __ ) this will mean for our example that it “does” for over the range A1:A10.
The If(Row(), __ ) I have seen many times, first in a Forum answer you gave.
The If({1}, __ ) I guessed, following those arguments should do the same and it did. I had not seen that before but I expect someone else stumbled over it as well a long time before me.
A few post down I suggested what happens when you do like_...
Evaluate("IF(Row(A1:A11),LEFT(A1:A10,5))")
The same reasoning suggest this will be “done” over A1:A11. The last evaluation returns an error, as expected.
The fact that the code below does not produce errors in a second “column” in the Array is explained as follows. Excel holds a single breadth range reference like A1:A10 , as I suggested above , in an effective help matrix extending across or down the “other” dimension
So the returned Array duplicates the results in the second “column”
In a way it is what happens in CSE type 2 array entry in which you extend the area you select ( before adding your formula and doing CSE ) to a spreadsheet area beyond the range of values you are expecting back.
https://usefulgyaan.wordpress.com/20...e/#comment-739Code:Sub ExtendedAcrossSingleBreadthRefPseudoCSEType2Entry() ' Dim Arr() As Variant Let Arr() = Evaluate("=IF(Row(A1:A10)*Column(A:B),LEFT(A1:A10,5))") ' Run code in Debug, F8 mode, stop at End Sub. - Highlight either Arr - Hit Shift+F9 - OK You will see in Watch window that Arr is a (1 to 10, 1 to 2) array. The "row" values are duplicated across the "columns" End Sub
https://www.mrexcel.com/forum/excel-...ml#post4375354
_...................................
That is all not a perfect answer, as it does not explain all situations*** such as The T(If(1,____)) stuff _...
https://excelxor.com/2014/09/05/inde.../#comment-2514
_... which recently gave me more hours of frustration than any of your one liners, which I am happy to say I think I mostly understand now.
It was healthy frustration, in that I learned much from you.
Alan
Reply With Quote
Bookmarks