Help in Trying to Understand Formulas of form “CSE Curly Bracket” that you have to input as an Array.

[DocAElstein]

Hi,<o></o>
. Like I guess many people I struggle a bit to understand these Formulas of form“CSE Curly Bracket” that you have to input as an Array. (I sort of gave up for a while trying to fully understand , and rather concentrated on at least getting to understand or “accept” “The rules” of using them form amongst others MrExcel Threads such as Post # 22 here to name but 1:<o></o>
http://www.mrexcel.com/forum/excel-...ltiple-values-matching-unique-criteria-3.html )<o></o>
…I then even surprised myself and managed to answer a last couple of threads with a quite complicated CSE Formula!!.<o></o>
.But I still do not quite get it and am desperate to get at least a bit more clarity in my head on these things ….<o></o>
. Along the way in getting the formulas I used in answering those last two Threads I noticed something ”odd”. Without understanding it I used what seemed to be happening there to develop the formulas I used.<o></o>
. Maybe if someone “in the know” could explain in as much detail as you may be able to what is going on in the next small example it could help go some way in clearing the mystery of these things up a bit. (It may be a bit clearer to follow the file I upload at the end rather than the screen shots )<o></o>
<o> </o>
…So a simple example: <o></o>
<o> </o>
In some arbritrary place in a spread sheet I type in the following<o></o>
<o> </o>

Book1
	G	H
26	1	5
27	2	2
28	3	7
29	4	8
ForMrExcelFred

……<o></o>
<o> </o>
If I do the following anywhere in the spreadsheet as long as all rows are the same then I get the same results with CSE and non CSE formulas.. <o></o>
<o> </o>
(. Note For CSE1 -- I highlight all 4cells. -– Hit F2. –- Enter Formula. –- Confirm with CSE<o></o>
………For CSE 2 -- I highlight just the first cell. -– Hit F2. –-Enter Formula. –- Confirm with CSE. --- Drag that first formula down to occupy all 4 cells<o></o>
. I am also not too clear on the difference here. Until now I have obtained similar results for both CSE variations. )<o></o>
<o> </o>

Book1
	I	J	K	L
25	CSE1	CSE2	NOT CSE	NOT CSE2
26	False	False	False	False
27	True	True	True	True
28	False	False	False	False
29	False	False	False	False
ForMrExcelFred

Cell Formulas
Range	Formula
K26	=IF(G26:G29=H26:H29,"True","False")
K27	=IF(G27:G30=H27:H30,"True","False")
K28	=IF(G28:G31=H28:H31,"True","False")
K29	=IF(G29:G32=H29:H32,"True","False")
L26	=IF(G26:G29=H26:H29,"True","False")
L27	=IF(G26:G29=H26:H29,"True","False")
L28	=IF(G26:G29=H26:H29,"True","False")
L29	=IF(G26:G29=H26:H29,"True","False")
I26:I29	{=IF(G26:G29=H26:H29,"True","False")}
J26	{=IF(G26:G29=H26:H29,"True","False")}
J27	{=IF(G27:G30=H27:H30,"True","False")}
J28	{=IF(G28:G31=H28:H31,"True","False")}
J29	{=IF(G29:G32=H29:H32,"True","False")}
Press CTRL+SHIFT+ENTER to enter array formulas.

…….<o></o>
<o> </o>
. If however I do something similar in the spreadsheet with any amount of offset in the rows such as here, then only the CSE’s work<o></o>
<o> </o>

Book1
	I	J	K	L
18	CSE1	CSE2	NOT CSE1	NOT CSE2
19	False	False	#WERT!	#WERT!
20	True	True	#WERT!	#WERT!
21	False	False	#WERT!	#WERT!
22	False	False	#WERT!	#WERT!
ForMrExcelFred

Cell Formulas
Range	Formula
K19	=IF(G26:G29=H26:H29,"True","False")
K20	=IF(G27:G30=H27:H30,"True","False")
K21	=IF(G28:G31=H28:H31,"True","False")
K22	=IF(G29:G32=H29:H32,"True","False")
L19	=IF(G26:G29=H26:H29,"True","False")
L20	=IF(G26:G29=H26:H29,"True","False")
L21	=IF(G26:G29=H26:H29,"True","False")
L22	=IF(G26:G29=H26:H29,"True","False")
I19:I22	{=IF(G26:G29=H26:H29,"True","False")}
J19	{=IF(G26:G29=H26:H29,"True","False")}
J20	{=IF(G27:G30=H27:H30,"True","False")}
J21	{=IF(G28:G31=H28:H31,"True","False")}
J22	{=IF(G29:G32=H29:H32,"True","False")}
Press CTRL+SHIFT+ENTER to enter array formulas.

<o> </o>
<o> </o>
<o> </o>
. I kind of saw enough logic in what was going on in order to be able to know when I needed to use the “CSE trick” to get my formulas to do what I wanted. But I do not quite understand how and why. And that is very frustrating. Googling has not helped, and I am finding some people here have a more in depth knowledge than any book.<o></o>
<o> </o>
. Can anyone here help me..<o></o>
<o> </o>
. Thanks<o></o>
. BTW. No rush on this one. Rather a good in depth answer when someone has the time.<o></o>
. Very much appreciated<o></o>
Alan<o></o>
<o> </o>
.P.s. 1. In case it helps and may be easier to follow, the File I gave/ used in developing one of those tricky CSE formulas I did which also has this example above in is here.<o></o>
https://app.box.com/s/r6ptaai08xobj5qx6irbvxhzfuunyjvn<o></o>
(XL2007: “VerticalToHorizontal.xlsx” Sheet of interest: Sheet6 name: “ForMrExcelFred” )<o></o>
<o> </o>
.P.s.2 I am an avid user nowadays of F9 to do an instant evaluation of parts of the formulas since this was explained to me. Usually it is a great help. But it is failing me here as it is suggesting (possibly naively on my behalf ) that my NOT CSE2 possibly should always work anywhere ), and also suggests to me that my CSE2 should not always work anywhere ( or at least in the second example should give me {False,False,True,True} !?!? )<o></o>

 

[RoryA]

CSE2, because it is array entered into one cell only, will return the first value from the array.

For the non CSE versions, Excel will try to use the intersection of the formula cell and the entire rows/columns of the referenced range if it can - but only if the intersection is a single cell. So if you refer to a range in one column, but put the formula below that range, it will fail; alongside it will work. Equally, if you refer to a range that has multiple rows and columns, the formula won't work anywhere unless you array enter it.

 

[DocAElstein]

CSE2, because it is array entered into one cell only, will return the first value from the array……..

. O.K. I was probably a bit careless / confused with my “F9 Evaluate in Formula Bar” investigations there and confusing CSE1 With CSE2. The results do tie up when I look again. Even so I would have had to “assume” that Excel was returning the first value. – That may have been then an “obvious” conclusion. -. But I did not manage to “Google” that one. So very helpful that you confirmed that. Thanks.

………………………………………___________________

………..
For the non CSE versions, Excel will try to use the intersection of the formula cell and the entire rows/columns of the referenced range if it can - but only if the intersection is a single cell. So if you refer to a range in one column, but put the formula below that range, it will fail; alongside it will work. ……..

. O.K. I think I am following that as well . - It is a case again of knowing wot Excel is doing. I don’t, you do (it may be written somewhere but I could not find it. If I get the point here, for each individual cell reference that is made, Excel sort of goes searching (in this case left and right as it is a range in one column) , and if it gets lucky and hits the correct Range it is looking for then all well and good. It is perhaps unfortunate that my NOT CSE1 column and NOT CSE2 column are giving the same results. They should indeed give the same results but the Ranges being referenced are different. There are 4 different ones in the Example NOT CSE2 and in NOT CSE1 the same ranges are referenced 4 times. (But your explanation is still valid.)
. If I may just attempt to explain that graphically, with the strength of the color indicating how often ranges are referenced:

NOT CSE 1:

Book1
	G	H	I
30			NOT CSE1
31	1	5	False
32	2	2	True
33	3	7	False
34	4	8	False
ForMrExcelFred

Cell Formulas
Range	Formula
I31	=IF(G31:G34=H31:H34,"True","False")
I32	=IF(G31:G34=H31:H34,"True","False")
I33	=IF(G31:G34=H31:H34,"True","False")
I34	=IF(G31:G34=H31:H34,"True","False")

………………………________________________


NOT CSE 2:

Book1
	G	H	I
36			NOT CSE2
37	1	5	False
38	2	2	True
39	3	7	False
40	4	8	False
41
42
43
ForMrExcelFred

Cell Formulas
Range	Formula
I37	=IF(G37:G40=H37:H40,"True","False")
I38	=IF(G38:G41=H38:H41,"True","False")
I39	=IF(G39:G42=H39:H42,"True","False")
I40	=IF(G40:G43=H40:H43,"True","False")

. Another answer to the above bit (which follows on nicely to the last bit below) is that there exists a sort of “implicit default” bit here on where Excel “goes – a – looking” when it sees a multiple rows or columns. This Implicit default in this case is according to the rules / explanation you gave.

…

So Finally:-

……. Equally, if you refer to a range that has multiple rows and columns, the formula won't work anywhere unless you array enter it.

. The CSE is not such a mystery at all (when someone in the know shares the “secret! – Thanks!). (And as I think I either read or dreamt I read , “ Excel does not really have Array formulas…” )..

. The CSE Curly bracket is not much more than explicitly telling Excel where to go.
.
. Correspondingly, and along a similar argument (sort of), something I did not dream
.

VBA doesn't really have array functions……...

( http://www.mrexcel.com/forum/excel-...ic-applications-evaluate-range-vlookup-4.html )

. So a final thought… In a spreadsheet this
{Here Stuff}

is similar to in VBA this
Array(This stuff),

with Here Stuff being got at by evaluating wot it is , or in the following case just taking wot it is (which I suppose is a form of evaluating as well):

…Spreadsheet: { “B” , “A” }

Is a similar idea to:

.. VBA : Array ( “B” , “A” )

. ….

Many thanks.

Alan

P.s. File again for anyone wanting to follow the “graphical explanation” a bit easier..
https://app.box.com/s/76jm70vxiirk93zaubhet4fsw10u3sv3