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

DocAElstein

Banned user
Joined
May 24, 2014
Messages
1,336
Hi,<o:p></o:p>
. 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:p></o:p>
http://www.mrexcel.com/forum/excel-...ltiple-values-matching-unique-criteria-3.html )<o:p></o:p>
…I then even surprised myself and managed to answer a last couple of threads with a quite complicated CSE Formula!!.<o:p></o:p>
.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:p></o:p>
. 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:p></o:p>
. 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:p></o:p>
<o:p> </o:p>
…So a simple example: <o:p></o:p>
<o:p> </o:p>
In some arbritrary place in a spread sheet I type in the following<o:p></o:p>
<o:p> </o:p>
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;background-color: #CADBA9;;">1</td><td style="text-align: center;background-color: #CADBA9;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;background-color: #CADBA9;;">2</td><td style="text-align: center;background-color: #CADBA9;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;background-color: #CADBA9;;">3</td><td style="text-align: center;background-color: #CADBA9;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;background-color: #CADBA9;;">4</td><td style="text-align: center;background-color: #CADBA9;;">8</td></tr></tbody></table><p style="width:8,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">ForMrExcelFred</p><br /><br />


……<o:p></o:p>

<o:p> </o:p>
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:p></o:p>
<o:p> </o:p>
(. Note For CSE1 -- I highlight all 4cells. -– Hit F2. –- Enter Formula. –- Confirm with CSE<o:p></o:p>
………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:p></o:p>
. I am also not too clear on the difference here. Until now I have obtained similar results for both CSE variations. )<o:p></o:p>
<o:p> </o:p>
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">CSE1</td><td style="text-align: center;;">CSE2</td><td style="text-align: center;;">NOT CSE</td><td style="text-align: center;;">NOT CSE2</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;background-color: #F2F6EA;;">False</td><td style="text-align: center;background-color: #E6EED6;;">False</td><td style="text-align: center;background-color: #E6EED6;;">False</td><td style="text-align: center;background-color: #F2F6EA;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;background-color: #F2F6EA;;">True</td><td style="text-align: center;background-color: #E6EED6;;">True</td><td style="text-align: center;background-color: #E6EED6;;">True</td><td style="text-align: center;background-color: #F2F6EA;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;background-color: #F2F6EA;;">False</td><td style="text-align: center;background-color: #E6EED6;;">False</td><td style="text-align: center;background-color: #E6EED6;;">False</td><td style="text-align: center;background-color: #F2F6EA;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;background-color: #F2F6EA;;">False</td><td style="text-align: center;background-color: #E6EED6;;">False</td><td style="text-align: center;background-color: #E6EED6;;">False</td><td style="text-align: center;background-color: #F2F6EA;;">False</td></tr></tbody></table><p style="width:8,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">ForMrExcelFred</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K26</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L26</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K27</th><td style="text-align:left">=IF(<font color="Blue">G27:G30=H27:H30,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L27</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K28</th><td style="text-align:left">=IF(<font color="Blue">G28:G31=H28:H31,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L28</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K29</th><td style="text-align:left">=IF(<font color="Blue">G29:G32=H29:H32,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L29</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I26:I29</th><td style="text-align:left">{=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J26</th><td style="text-align:left">{=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J27</th><td style="text-align:left">{=IF(<font color="Blue">G27:G30=H27:H30,"True","False"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J28</th><td style="text-align:left">{=IF(<font color="Blue">G28:G31=H28:H31,"True","False"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J29</th><td style="text-align:left">{=IF(<font color="Blue">G29:G32=H29:H32,"True","False"</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />



…….<o:p></o:p>
<o:p> </o:p>
. 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:p></o:p>
<o:p> </o:p>
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">CSE1</td><td style="text-align: center;;">CSE2</td><td style="text-align: center;;">NOT CSE1</td><td style="text-align: center;;">NOT CSE2</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;background-color: #F2F6EA;;">False</td><td style="text-align: center;background-color: #F2F6EA;;">False</td><td style="text-align: center;background-color: #E6EED6;;">#WERT!</td><td style="text-align: center;background-color: #F2F6EA;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;background-color: #F2F6EA;;">True</td><td style="text-align: center;background-color: #E6EED6;;">True</td><td style="text-align: center;background-color: #E6EED6;;">#WERT!</td><td style="text-align: center;background-color: #F2F6EA;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;background-color: #F2F6EA;;">False</td><td style="text-align: center;background-color: #E6EED6;;">False</td><td style="text-align: center;background-color: #E6EED6;;">#WERT!</td><td style="text-align: center;background-color: #F2F6EA;;">#WERT!</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;background-color: #F2F6EA;;">False</td><td style="text-align: center;background-color: #E6EED6;;">False</td><td style="text-align: center;background-color: #E6EED6;;">#WERT!</td><td style="text-align: center;background-color: #F2F6EA;;">#WERT!</td></tr></tbody></table><p style="width:8,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">ForMrExcelFred</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K19</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L19</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K20</th><td style="text-align:left">=IF(<font color="Blue">G27:G30=H27:H30,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L20</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K21</th><td style="text-align:left">=IF(<font color="Blue">G28:G31=H28:H31,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L21</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">K22</th><td style="text-align:left">=IF(<font color="Blue">G29:G32=H29:H32,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">L22</th><td style="text-align:left">=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I19:I22</th><td style="text-align:left">{=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J19</th><td style="text-align:left">{=IF(<font color="Blue">G26:G29=H26:H29,"True","False"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J20</th><td style="text-align:left">{=IF(<font color="Blue">G27:G30=H27:H30,"True","False"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J21</th><td style="text-align:left">{=IF(<font color="Blue">G28:G31=H28:H31,"True","False"</font>)}</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J22</th><td style="text-align:left">{=IF(<font color="Blue">G29:G32=H29:H32,"True","False"</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
. 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:p></o:p>
<o:p> </o:p>
. Can anyone here help me..<o:p></o:p>
<o:p> </o:p>
. Thanks<o:p></o:p>
. BTW. No rush on this one. Rather a good in depth answer when someone has the time.<o:p></o:p>
. Very much appreciated<o:p></o:p>
Alan<o:p></o:p>
<o:p> </o:p>
.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:p></o:p>
https://app.box.com/s/r6ptaai08xobj5qx6irbvxhzfuunyjvn<o:p></o:p>
(XL2007: “VerticalToHorizontal.xlsx” Sheet of interest: Sheet6 name: “ForMrExcelFred” )<o:p></o:p>
<o:p> </o:p>
.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:p></o:p>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Banned user
Joined
May 24, 2014
Messages
1,336
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:



<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">NOT CSE1</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;background-color: #5A702E;;">1</td><td style="text-align: center;background-color: #5A702E;;">5</td><td style="text-align: center;background-color: #5A702E;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;background-color: #5A702E;;">2</td><td style="text-align: center;background-color: #5A702E;;">2</td><td style="text-align: center;background-color: #5A702E;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;background-color: #5A702E;;">3</td><td style="text-align: center;background-color: #5A702E;;">7</td><td style="text-align: center;background-color: #5A702E;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;background-color: #5A702E;;">4</td><td style="text-align: center;background-color: #5A702E;;">8</td><td style="text-align: center;background-color: #5A702E;;">False</td></tr></tbody></table><p style="width:8,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">ForMrExcelFred</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I31</th><td style="text-align:left">=IF(<font color="Blue">G31:G34=H31:H34,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I32</th><td style="text-align:left">=IF(<font color="Blue">G31:G34=H31:H34,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I33</th><td style="text-align:left">=IF(<font color="Blue">G31:G34=H31:H34,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I34</th><td style="text-align:left">=IF(<font color="Blue">G31:G34=H31:H34,"True","False"</font>)</td></tr></tbody></table></td></tr></table><br />



………………………________________________


NOT CSE 2:


<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">NOT CSE2</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: center;background-color: #F0F5E7;;">1</td><td style="text-align: center;background-color: #F0F5E7;;">5</td><td style="text-align: center;background-color: #F0F5E7;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: center;background-color: #C2D69A;;">2</td><td style="text-align: center;background-color: #CADBA9;;">2</td><td style="text-align: center;background-color: #CADBA9;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: center;background-color: #93B64E;;">3</td><td style="text-align: center;background-color: #93B64E;;">7</td><td style="text-align: center;background-color: #93B64E;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: center;background-color: #5A702E;;">4</td><td style="text-align: center;background-color: #5A702E;;">8</td><td style="text-align: center;background-color: #5A702E;;">False</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: center;background-color: #93B64E;;"></td><td style="text-align: center;background-color: #93B64E;;"></td><td style="text-align: center;background-color: #93B64E;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: center;background-color: #C2D69A;;"></td><td style="text-align: center;background-color: #C2D69A;;"></td><td style="text-align: center;background-color: #C2D69A;;"></td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style="text-align: center;background-color: #F0F5E7;;"></td><td style="text-align: center;background-color: #F0F5E7;;"></td><td style="text-align: center;background-color: #F0F5E7;;"></td></tr></tbody></table><p style="width:8,4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">ForMrExcelFred</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I37</th><td style="text-align:left">=IF(<font color="Blue">G37:G40=H37:H40,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I38</th><td style="text-align:left">=IF(<font color="Blue">G38:G41=H38:H41,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I39</th><td style="text-align:left">=IF(<font color="Blue">G39:G42=H39:H42,"True","False"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">I40</th><td style="text-align:left">=IF(<font color="Blue">G40:G43=H40:H43,"True","False"</font>)</td></tr></tbody></table></td></tr></table><br />



. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,590
Members
417,152
Latest member
DayTimeSeby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top