DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
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
>
……<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
>
…….<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
>
<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
>
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. 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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
http://www.mrexcel.com/forum/excel-...ltiple-values-matching-unique-criteria-3.html )<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
…I then even surprised myself and managed to answer a last couple of threads with a quite complicated CSE Formula!!.<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
.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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. 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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. 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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
…So a simple example: <o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
In some arbritrary place in a spread sheet I type in the following<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Book1 | ||||
---|---|---|---|---|
G | H | |||
26 | 1 | 5 | ||
27 | 2 | 2 | ||
28 | 3 | 7 | ||
29 | 4 | 8 | ||
ForMrExcelFred |
……<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
(. Note For CSE1 -- I highlight all 4cells. -– Hit F2. –- Enter Formula. –- Confirm with CSE<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
………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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. I am also not too clear on the difference here. Until now I have obtained similar results for both CSE variations. )<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. 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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. 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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. Can anyone here help me..<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. Thanks<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. BTW. No rush on this one. Rather a good in depth answer when someone has the time.<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
. Very much appreciated<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
Alan<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
.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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
https://app.box.com/s/r6ptaai08xobj5qx6irbvxhzfuunyjvn<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
(XL2007: “VerticalToHorizontal.xlsx” Sheet of interest: Sheet6 name: “ForMrExcelFred” )<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
<o
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
.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
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)
![Stick out tongue :p :p](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f61b.png)