Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 4th 06, 07:41 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default 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   Report Post  
Old September 4th 06, 08:30 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,393
Default 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   Report Post  
Old September 4th 06, 09:15 PM posted to microsoft.public.excel.newusers
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




  #4   Report Post  
Old September 4th 06, 09:27 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 3,572
Default 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 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





  #5   Report Post  
Old September 4th 06, 11:57 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
Posts: 605
Default Array formulas and braces { }

"Also, try it without Ctrl-Shift-Enter."

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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
A Difficult Unconcatinate Problem RestlessAde Excel Discussion (Misc queries) 10 May 30th 07 08:11 PM
array formulas and getformula UDF Dave F Excel Worksheet Functions 8 August 10th 06 09:32 PM
Populating a field based on lookup values Sav_C Excel Worksheet Functions 5 August 6th 06 02:49 AM
Unprotecting a Sheet SergioCorreiaMaputo Excel Discussion (Misc queries) 3 June 20th 06 10:56 AM
IF Formula to go through every line of array Leibtek Excel Worksheet Functions 5 March 9th 06 06:42 PM


All times are GMT +1. The time now is 03:02 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017