What is this function? { }
 

I have a worksheet with formulas that need some corrections. They have these
Brackets around them and as soon as I start to edit (the dates are wrong) the
function becomes inoperational. I can't find an explanation of what these are
and would love to know how to work around them with out disabling all the
current funtionality.

Here is an example:

{=SUM(IF(Open!$R$4:$R$1001=DATEVALUE("1/31/2009"),IF(Open!$R$4:$R$1001<=DATEVALUE("2/27/2009"),IF(Open!$F$4:$F$1001=J4,Open!$W$4:$W$1001,0 ),0)))}

As soon as I click within the cell the outside brackets { } are gone and the
function no longer works. Is this a lock out function of some type?

What is this function? { }
 

On Fri, 13 Feb 2009 16:35:01 -0800, cricket
wrote:

I have a worksheet with formulas that need some corrections. They have these
Brackets around them and as soon as I start to edit (the dates are wrong) the
function becomes inoperational. I can't find an explanation of what these are
and would love to know how to work around them with out disabling all the
current funtionality.

Here is an example:

{=SUM(IF(Open!$R$4:$R$1001=DATEVALUE("1/31/2009"),IF(Open!$R$4:$R$1001<=DATEVALUE("2/27/2009"),IF(Open!$F$4:$F$1001=J4,Open!$W$4:$W$1001,0 ),0)))}

As soon as I click within the cell the outside brackets { } are gone and the
function no longer works. Is this a lock out function of some type?

Read about array formulas e.g. here
http://www.cpearson.com/excel/ArrayFormulas.aspx

Hope this helps / Lars-Åke

What is this function? { }
 

This was perfect! Thank You!!!! makes short order of the fix when you get a
clue to what's going on! : ) - Cricket

"Lars-Ã…ke Aspelin" wrote:

On Fri, 13 Feb 2009 16:35:01 -0800, cricket
wrote:

I have a worksheet with formulas that need some corrections. They have these
Brackets around them and as soon as I start to edit (the dates are wrong) the
function becomes inoperational. I can't find an explanation of what these are
and would love to know how to work around them with out disabling all the
current funtionality.

Here is an example:

{=SUM(IF(Open!$R$4:$R$1001=DATEVALUE("1/31/2009"),IF(Open!$R$4:$R$1001<=DATEVALUE("2/27/2009"),IF(Open!$F$4:$F$1001=J4,Open!$W$4:$W$1001,0 ),0)))}

As soon as I click within the cell the outside brackets { } are gone and the
function no longer works. Is this a lock out function of some type?

Read about array formulas e.g. here
http://www.cpearson.com/excel/ArrayFormulas.aspx

Hope this helps / Lars-Ã…ke

What is this function? { }
 

That formula looks like it was created using the Conditional Sum Wizzard.

It can be written so that you don't need to array enter it and will give you
more flexibilty to change dates whenever the need arises.

Use cells to hold the date boundaries:

A1 = lower date boundry = 1/31/2009
B1 = upper date boundary = 2/27/2009

Then, normally entered (just hit ENTER):

=SUMPRODUCT(--(Open!$F$4:$F$1001=J4),--(Open!$R$4:$R$1001=A1),--(Open!$R$4:$R$1001<=B1),Open!$W$4:$W$1001)


--
Biff
Microsoft Excel MVP


"cricket" wrote in message
...
This was perfect! Thank You!!!! makes short order of the fix when you get
a
clue to what's going on! : ) - Cricket

"Lars-Åke Aspelin" wrote:

On Fri, 13 Feb 2009 16:35:01 -0800, cricket
wrote:

I have a worksheet with formulas that need some corrections. They have
these
Brackets around them and as soon as I start to edit (the dates are
wrong) the
function becomes inoperational. I can't find an explanation of what
these are
and would love to know how to work around them with out disabling all
the
current funtionality.

Here is an example:

{=SUM(IF(Open!$R$4:$R$1001=DATEVALUE("1/31/2009"),IF(Open!$R$4:$R$1001<=DATEVALUE("2/27/2009"),IF(Open!$F$4:$F$1001=J4,Open!$W$4:$W$1001,0 ),0)))}

As soon as I click within the cell the outside brackets { } are gone and
the
function no longer works. Is this a lock out function of some type?

Read about array formulas e.g. here
http://www.cpearson.com/excel/ArrayFormulas.aspx

Hope this helps / Lars-Åke