View Single Post
  #3   Report Post  
Old September 4th 06, 09:15 PM posted to microsoft.public.excel.newusers
Bob Phillips Bob Phillips is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default 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 hard-coding them you need to tell Excel.

Also, try it without Ctrl-Shift-Enter.

--
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