Home 
Search 
Today's Posts 
#1




Array formulas and braces { }
I have no problem entering the following array formula, but I still have
some questions. {=INDEX({1,2;3,4},0,2)} I understand that the { } for array formulas are included by pressing Ctrl+Shift+Enter. That was what I did with the OUTER braces (the ones enclosing the entire formula); no problem. For the INNER braces (the ones around 1,2;3,4), I thought I had to use Ctrl+Shift+Enter. But of course I couldn't and had to key in the { } MANUALLY. The system won't accept ( ) in the place of { }. Guess we must use { } to indicate an array but no need to use Ctrl+Shift+Enter when it is an array and NOT an array FORMULA. Am I right? I **was** quite confused as to when to use Ctrl+Shift+Enter and when to key in the { } manually. There is quite a bit of trick to enter the above formula. I did the following. Position my cursor in A1 and key in MANUALLY =INDEX({1,2;3,4},0,2) Press enter. Select A1 **and A2**, press F2, press Ctrl+Shift+Enter. (Note: A2 is a blank cell.) The outer braces will now be displayed as part of the formula. A1 shows: 2 A2 shows: 4 Appreciate clarification on keying in the { }. Epinn 
#2




Array formulas and braces { }
You have correctly analysed the problem
best wishes  Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Epinn" wrote in message ... I have no problem entering the following array formula, but I still have some questions. {=INDEX({1,2;3,4},0,2)} I understand that the { } for array formulas are included by pressing Ctrl+Shift+Enter. That was what I did with the OUTER braces (the ones enclosing the entire formula); no problem. For the INNER braces (the ones around 1,2;3,4), I thought I had to use Ctrl+Shift+Enter. But of course I couldn't and had to key in the { } MANUALLY. The system won't accept ( ) in the place of { }. Guess we must use { } to indicate an array but no need to use Ctrl+Shift+Enter when it is an array and NOT an array FORMULA. Am I right? I **was** quite confused as to when to use Ctrl+Shift+Enter and when to key in the { } manually. There is quite a bit of trick to enter the above formula. I did the following. Position my cursor in A1 and key in MANUALLY =INDEX({1,2;3,4},0,2) Press enter. Select A1 **and A2**, press F2, press Ctrl+Shift+Enter. (Note: A2 is a blank cell.) The outer braces will now be displayed as part of the formula. A1 shows: 2 A2 shows: 4 Appreciate clarification on keying in the { }. Epinn 
#3




Array formulas and braces { }
The inner array is an array constant, and is required as it is not the whole
formula that is an array, but just those values. INDEX expects an array of values, so if hardcoding them you need to tell Excel. Also, try it without CtrlShiftEnter.  HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... I have no problem entering the following array formula, but I still have some questions. {=INDEX({1,2;3,4},0,2)} I understand that the { } for array formulas are included by pressing Ctrl+Shift+Enter. That was what I did with the OUTER braces (the ones enclosing the entire formula); no problem. For the INNER braces (the ones around 1,2;3,4), I thought I had to use Ctrl+Shift+Enter. But of course I couldn't and had to key in the { } MANUALLY. The system won't accept ( ) in the place of { }. Guess we must use { } to indicate an array but no need to use Ctrl+Shift+Enter when it is an array and NOT an array FORMULA. Am I right? I **was** quite confused as to when to use Ctrl+Shift+Enter and when to key in the { } manually. There is quite a bit of trick to enter the above formula. I did the following. Position my cursor in A1 and key in MANUALLY =INDEX({1,2;3,4},0,2) Press enter. Select A1 **and A2**, press F2, press Ctrl+Shift+Enter. (Note: A2 is a blank cell.) The outer braces will now be displayed as part of the formula. A1 shows: 2 A2 shows: 4 Appreciate clarification on keying in the { }. Epinn 
#4




Array formulas and braces { }
Am I missing something?
How do you get 2 and 4 returned without CSE?  Regards, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Bob Phillips" wrote in message ... The inner array is an array constant, and is required as it is not the whole formula that is an array, but just those values. INDEX expects an array of values, so if hardcoding them you need to tell Excel. Also, try it without CtrlShiftEnter.  HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... I have no problem entering the following array formula, but I still have some questions. {=INDEX({1,2;3,4},0,2)} I understand that the { } for array formulas are included by pressing Ctrl+Shift+Enter. That was what I did with the OUTER braces (the ones enclosing the entire formula); no problem. For the INNER braces (the ones around 1,2;3,4), I thought I had to use Ctrl+Shift+Enter. But of course I couldn't and had to key in the { } MANUALLY. The system won't accept ( ) in the place of { }. Guess we must use { } to indicate an array but no need to use Ctrl+Shift+Enter when it is an array and NOT an array FORMULA. Am I right? I **was** quite confused as to when to use Ctrl+Shift+Enter and when to key in the { } manually. There is quite a bit of trick to enter the above formula. I did the following. Position my cursor in A1 and key in MANUALLY =INDEX({1,2;3,4},0,2) Press enter. Select A1 **and A2**, press F2, press Ctrl+Shift+Enter. (Note: A2 is a blank cell.) The outer braces will now be displayed as part of the formula. A1 shows: 2 A2 shows: 4 Appreciate clarification on keying in the { }. Epinn 
#5




Array formulas and braces { }
"Also, try it without CtrlShiftEnter."
I hope this is just a suggestion to experiment and see the different results between an array formula (C+S+E) and a "regular" formula (without C+S+E). I hope Bob is not saying that without C+S+E we will get 2 and 4. I have been playing with the formulas and {} quite a bit and I don't want to confuse myself anymore than I should. The conclusion of my findings is the following. Array formula with the braces: result = 2 and 4 A formula without the braces: result = 2 I have verified that the information in Help is correct. You know, sometimes Help is wrong and I think I have just found a situation to substantiate my claim. I am going to start a new thread. If someone backs me up, then I'll submit my findings to MS. Thank you for your attention. Epinn "Ragdyer" wrote in message ... Am I missing something? How do you get 2 and 4 returned without CSE?  Regards, RD   Please keep all correspondence within the NewsGroup, so all may benefit !   "Bob Phillips" wrote in message ... The inner array is an array constant, and is required as it is not the whole formula that is an array, but just those values. INDEX expects an array of values, so if hardcoding them you need to tell Excel. Also, try it without CtrlShiftEnter.  HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Epinn" wrote in message ... I have no problem entering the following array formula, but I still have some questions. {=INDEX({1,2;3,4},0,2)} I understand that the { } for array formulas are included by pressing Ctrl+Shift+Enter. That was what I did with the OUTER braces (the ones enclosing the entire formula); no problem. For the INNER braces (the ones around 1,2;3,4), I thought I had to use Ctrl+Shift+Enter. But of course I couldn't and had to key in the { } MANUALLY. The system won't accept ( ) in the place of { }. Guess we must use { } to indicate an array but no need to use Ctrl+Shift+Enter when it is an array and NOT an array FORMULA. Am I right? I **was** quite confused as to when to use Ctrl+Shift+Enter and when to key in the { } manually. There is quite a bit of trick to enter the above formula. I did the following. Position my cursor in A1 and key in MANUALLY =INDEX({1,2;3,4},0,2) Press enter. Select A1 **and A2**, press F2, press Ctrl+Shift+Enter. (Note: A2 is a blank cell.) The outer braces will now be displayed as part of the formula. A1 shows: 2 A2 shows: 4 Appreciate clarification on keying in the { }. Epinn 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
A Difficult Unconcatinate Problem  Excel Discussion (Misc queries)  
array formulas and getformula UDF  Excel Worksheet Functions  
Populating a field based on lookup values  Excel Worksheet Functions  
Unprotecting a Sheet  Excel Discussion (Misc queries)  
IF Formula to go through every line of array  Excel Worksheet Functions 