Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |