Prev: How do you hide/un-hide the grid lines
Next: Quick formula help please
From: GeorgeA on 19 May 2010 13:12
I have a formula that shows as:
{=A5-INDEX(A5:A400,MATCH(TRUE,K5:K400>0,0))}

It works but I want to change it to:
{=A5-INDEX(A5:A400,MATCH(TRUE,J5:J400>B6,0))}

Problem is that when I click to edit the formula, the {} brackets disappear
and then the formula returns $VALUE!.

Why do these brackets show up and how do I edit this formula?

Thanks, any help would be appreciated!

From: Homey on 19 May 2010 13:39
after you chang formuls dont just press enter. instead press
ctrl-shift-enter. this is call 'array-entering'.

"GeorgeA" <GeorgeA(a)discussions.microsoft.com> wrote in message
news:AEE49CB4-577B-40E4-9B1F-626FFD2204E2(a)microsoft.com...
|I have a formula that shows as:
| {=A5-INDEX(A5:A400,MATCH(TRUE,K5:K400>0,0))}
|
| It works but I want to change it to:
| {=A5-INDEX(A5:A400,MATCH(TRUE,J5:J400>B6,0))}
|
| Problem is that when I click to edit the formula, the {} brackets
disappear
| and then the formula returns $VALUE!.
|
| Why do these brackets show up and how do I edit this formula?
|
| Thanks, any help would be appreciated!
|

From: Chip Pearson on 19 May 2010 14:03
The curly braces { } indicate that the formula is an "array
formula". To make a formula an array formula, you must press CTRL
SHIFT ENTER rather than just ENTER when you first enter the formula
and whenever you edit it later. You don't type in the braces; Excel
puts them in automatically. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com


On Wed, 19 May 2010 10:12:01 -0700, GeorgeA
<GeorgeA(a)discussions.microsoft.com> wrote:

>I have a formula that shows as:
>{=A5-INDEX(A5:A400,MATCH(TRUE,K5:K400>0,0))}
>
>It works but I want to change it to:
>{=A5-INDEX(A5:A400,MATCH(TRUE,J5:J400>B6,0))}
>
>Problem is that when I click to edit the formula, the {} brackets disappear
>and then the formula returns $VALUE!.
>
>Why do these brackets show up and how do I edit this formula?
>
>Thanks, any help would be appreciated!
From: Eduardo on 19 May 2010 14:06
Hi
Edidt the formula, go to the cell where the formula is, press F2, then press
CTRL+SHIFT+ENTER

that will do the trick

"GeorgeA" wrote:

> I have a formula that shows as:
> {=A5-INDEX(A5:A400,MATCH(TRUE,K5:K400>0,0))}
>
> It works but I want to change it to:
> {=A5-INDEX(A5:A400,MATCH(TRUE,J5:J400>B6,0))}
>
> Problem is that when I click to edit the formula, the {} brackets disappear
> and then the formula returns $VALUE!.
>
> Why do these brackets show up and how do I edit this formula?
>
> Thanks, any help would be appreciated!
>
From: GeorgeA on 19 May 2010 14:32
That's it! Great to know, thanks!

"Eduardo" wrote:

> Hi
> Edidt the formula, go to the cell where the formula is, press F2, then press
> CTRL+SHIFT+ENTER
>
> that will do the trick
>
> "GeorgeA" wrote:
>
> > I have a formula that shows as:
> > {=A5-INDEX(A5:A400,MATCH(TRUE,K5:K400>0,0))}
> >
> > It works but I want to change it to:
> > {=A5-INDEX(A5:A400,MATCH(TRUE,J5:J400>B6,0))}
> >
> > Problem is that when I click to edit the formula, the {} brackets disappear
> > and then the formula returns $VALUE!.
> >
> > Why do these brackets show up and how do I edit this formula?
> >
> > Thanks, any help would be appreciated!
> >
 |  Next  |  Last
Pages: 1 2
Prev: How do you hide/un-hide the grid lines
Next: Quick formula help please