Getting our Rept(str, how_many_times) with multiple values to work. I forgot why.
Killings Interception: Suspending, ( or stopping at the first of ), Multi value return analysis.
Remove the Excuse for an Abortion
Using the same screen shots data as the last posts and running through some more_.....
| Row\Col |
I |
J |
K |
|---|
22 |
A |
- |
1 |
23 |
B |
- |
2 |
24 |
C |
- |
3 |
)
Rem 1 Going again to find that we only get one value from Evaluate("=REPT({"A";"B"},2)"), which is "AA"
Rem 2 Trying to get both possible values out.
We have already established , via CSE or F9 in formula bar, that we have two values available. But how do we get them?
'2a) review Excel VBA multi value analysis
We discussed in detail in the aside, ( ' '_- Asside: Range referencing in a cell, Range object referencing in a Ex cell, Excel is doing that., Evaluating it as such, Externally referencing a cell such, Cel, cel as a Range object is External to the cel, sort of Ex cel, or Excel ' the fundamental idea of a Excel "holding" a cell, through a string reference, as a Range object http://www.excelforum.com/developmen...ml#post4595462 http://www.eileenslounge.com/viewtop...202227#p202227 ) , that we can choose to get the full
210 Range object
or
220 its values held in ( for more than one cell ) a Field ( Array ) of Variant types.
230-270 Once Excel does not recognise this as a reference a Formula is recognised and simple range references are taken as appropriate dimensioned Arrays holding their cell values
The final output will be set to a dimension of pseudo "Open Window" to encompass the largest of any of the Arrays.
'2a(ii) When there is a mismatch in Array sizes,
_(i) 280 for example when a 3 x 1 ( 1,1 2,1 3,1 ) and a 2 x 1 ( 1,1 2,1 ) are present, then a fairly predictable result is obtained of an error in the 3, 1 output Array as Excel is trying to concatenate to nothing.
'Interception
_(ii) 300 ' Single value .
Results are less obvious for the case of one Array being a single element, or even if that is replaced by
320 A single value. In these cases we see a duplicated taken copy of that single value to concatenate rather than as we might of expected an error due to nothing there
_(iii) 340 ' Single breadth .
Similarly extending the pseudo "Open Window" such that a single breadth Array has a missing counterpart in the extended breadth will duplicate rather than erroring, ( but only up to the depth ) of the single breadth Array.
The above results, in particular the last two require the understanding of Intercept Theory which I wrote: 'Interception Theory:
http://www.excelfox.com/forum/showth...on-and-VLookUp
https://www.excelforum.com/tips-and-...d-vlookup.html
In brief , 'Interception Theory: Excel appears_..
_.. (ii) for the case of a Single value, to "hold" a help matrix of size of all excel spreadsheet cells to speed up interception of that with the effective "open window for intercept"
and
_..(iii) for the case of a Single "breadth" Array , to hold duplicated values extending outside the single breadth, "across" as it were all the spreadsheet cells , restricted to the "length" or "depth" of the Array. ( The Latter, (iii) , is responsible for Implicit Intersection ).
'2b) Attempting concatenations of 2 x 1 Arrays with Rept(__;__ , how_many_times) .
('2b(ii) Killing Interceptions )
The last code section demonstrated that Excel is "opening up a window" or "Array space" to encompass the largest dimensions of all seen Arrays. The point of this section is to try to replace one of the two references in the last code lines with the REPT(I22:I23,2) or REPT({"A";"B"},2), with the hope that somehow Excel would somehow find the I22:I23 or {"A";"B"}. Should this work, then we could concatenate to an empty Range or to {"";""} and so have a successful "coercion"
It didn't work
I had a feeling it would not
Never mind.
I expect this is telling me that each section in a & is being evaluated separately.. ( and I am somehow Killing Interception **)
But, and this is quite interesting, we are getting from REPT({"A";"B"},2) just the first value AA, but if I replace REPT({"A";"B"},2) with AA,
530, Then I get again my result as explained by Interception Theory, {"AAA";"BAA"}
550 Going back and using REPT("A",2) in place of REPT({"A";"B"},2) and once again I do get my result as explained by Interception Theory, {"AAA";"BAA"}. Very strange ** Somehow REPT({"A";"B"},2) KILLED interception.
I do not really know what is going on here , with killing interception. Possibly Excel is starting the process to which Controlled Shifting before Entering . This preliminary step is possibly suspending, ( or stopping at the first of ), Multi value return analysis.
So maybe we have a new idea : "Killings Interception: Suspending, ( or stopping at the first of ), Multi value return analysis. " or "An excuse for an abortion"
_._____________
'2c) Multivalve from REPT({"A";"B"},2)
One thing is clear:.. It is not at all clear the exact processes that are going on.
I suspect up until now, a lot of published work, workarounds, are based on empirical measurements. As a general rule, it would appear that including the "Excuse for an Abortion Function" in a complete Formula in which has valid returning multi value properties will somehow retrieve those values. But why it should retrieve the appropriate individual values and not then do a Interception evaluation with the first value ( multiple thereof ) , is not clear..
Standard Wonks
In any case we have the ( probably empirically derived ) standard wonks that work in most cases.
'2c)(i), '2c)(ii), '2c)(iii)
These are just a few possible ways to incorporate the Excuse for an Abortion Function , REPT({"A";"B"},2) into a total formula that will not change the final result, but includes another function which does in general return multiple values.
'2c)(i)
We discussed this here: https://www.excelforum.com/developme...ml#post4595462 . We showed that Index can be used to return multi cell ranges or array of values of contents thereof. In an extreme case we simply use it to return the entire first argument grid. When, as in this case , that forst argument is our Excuse for an Abortion Function, then somehow an extra pre evaluation appears to be going on of the F9 type to bring out the multiple values. The trick is that we have embedded it inside another function... ( So why is that? https://www.excelforum.com/excel-for...ml#post4601913
'2c)(ii) Transpose
Once again the expected first argument is expected to be, and wired to look for, and possibly evaluate to, multivalves.
'2c(iii) If( condition, do it then if condition is True )
This generally is a two argument If statement resulting in the second argument being done if the first argument condition is met. Once again no one really understands this fully.
A typical fist argument is Row(). A likely explanation:
_ In a spreadsheet , I believe this does an ( Inverse ) Intercept case 1 single value type process. In a spreadsheet it reveals the row number in which the formula =Row() is written. In evaluate it has lost some connection in the spreadsheet. In any case, we are just using it to get a True. As such I believe it sort of floats or transgresses all , waiting to be tied down. The use of it in If(__ will always cause the second argument expression to be done. The dimension of the second argument will define the final Output window dimension. Possibly indirectly this returns for Row() an Array of Long numbers greater than 1, which as can be seen in '2c(v), will be taken as 1 or True
_
'2c(iv)
A version of the previous If( condition, do it then if condition is True ), with specific row or column in the argument.
Here, an Array size is determined by the first argument. This will therefore contribute to the final Open window to encompassing maximum dimensions of all contributing Arrays
The values in the Array are not important, as long as they are greater than 0 then they are taken as 1 or True ( 930, 935, 970 )
Reply With Quote
Bookmarks