Home |
Search |
Today's Posts |
#1
|
|||
|
|||
complex function
I built a function some months ago, which works perfectly.
The difficulty is that the entire text in the cell is bracketed by "curly brackets" ( { and } ). When I modify anything in the cell, those brackets go away and the formula, even if I haven't actually changed anything, no longer works. I vaguely recall that there was some special keystroke combination or yoga position I had to use when saving the changes in the cell, in order to not disturb the curly brackets, but I cannot recall what they may be. Here is the content of the cell when it's working, if it helps any: {=SUM(IF(AF$4:AF$4000="A",IF(X$4:X$4000="Y",IF(AC$ 4:AC$4000="DM",AE$4:AE$4000,0),0),0))+SUM(IF(AF$4: AF$4000="A",IF(X$4:X$4000="I",IF(AC$4:AC$4000="DM" ,AE$4:AE$4000,0),0),0))} Thanks for your help!!! -DJW |
#2
|
|||
|
|||
Ctrl+Shift+Enter (or CSE) is an Array formula. :)
************ Anne Troy www.OfficeArticles.com "GTUGoddess" wrote in message ... I built a function some months ago, which works perfectly. The difficulty is that the entire text in the cell is bracketed by "curly brackets" ( { and } ). When I modify anything in the cell, those brackets go away and the formula, even if I haven't actually changed anything, no longer works. I vaguely recall that there was some special keystroke combination or yoga position I had to use when saving the changes in the cell, in order to not disturb the curly brackets, but I cannot recall what they may be. Here is the content of the cell when it's working, if it helps any: {=SUM(IF(AF$4:AF$4000="A",IF(X$4:X$4000="Y",IF(AC$ 4:AC$4000="DM",AE$4:AE$4000,0),0),0))+SUM(IF(AF$4: AF$4000="A",IF(X$4:X$4000="I",IF(AC$4:AC$4000="DM" ,AE$4:AE$4000,0),0),0))} Thanks for your help!!! -DJW |
#3
|
|||
|
|||
GTUGoddess wrote: I built a function some months ago, which works perfectly. The difficulty is that the entire text in the cell is bracketed by "curly brackets" ( { and } ). When I modify anything in the cell, those brackets go away and the formula, even if I haven't actually changed anything, no longer works. I vaguely recall that there was some special keystroke combination or yoga position I had to use when saving the changes in the cell, in order to not disturb the curly brackets, but I cannot recall what they may be. Here is the content of the cell when it's working, if it helps any: {=SUM(IF(AF$4:AF$4000="A",IF(X$4:X$4000="Y", IF(AC$4:AC$4000="DM",AE$4:AE$4000,0),0),0))+ SUM(IF(AF$4:AF$4000="A", IF(X$4:X$4000="I",IF(AC$4:AC$4000="DM",AE$4:AE$400 0,0),0),0))} Thanks for your help!!! -DJW You can avoid 'yoga position' with: =SUMPRODUCT(--(AF$4:AF$4000="A"),--ISNUMBER(MATCH(X$4:X$4000,{"I","Y"},0)),--(AC$4:AC$4000="DM"),AE$4:AE$4000) which needs just enter (instead of control+shift+enter your original formula requires). |
#4
|
|||
|
|||
Have a look here. http://www.exceltip.com/st/Array_For..._Side/126.html -- Flintstone ------------------------------------------------------------------------ Flintstone's Profile: http://www.excelforum.com/member.php...o&userid=15310 View this thread: http://www.excelforum.com/showthread...hreadid=469030 |
#5
|
|||
|
|||
Anne, Aladin and Flintstone,
Thank you all so much! I think I'll stick with CSE for now, given the time crunch, but I definitely want to try Aladin's suggestion when I have a few moments to play with it. Thanks again!!!! -DJW (GTUGoddess) "GTUGoddess" wrote: I built a function some months ago, which works perfectly. The difficulty is that the entire text in the cell is bracketed by "curly brackets" ( { and } ). When I modify anything in the cell, those brackets go away and the formula, even if I haven't actually changed anything, no longer works. I vaguely recall that there was some special keystroke combination or yoga position I had to use when saving the changes in the cell, in order to not disturb the curly brackets, but I cannot recall what they may be. Here is the content of the cell when it's working, if it helps any: {=SUM(IF(AF$4:AF$4000="A",IF(X$4:X$4000="Y",IF(AC$ 4:AC$4000="DM",AE$4:AE$4000,0),0),0))+SUM(IF(AF$4: AF$4000="A",IF(X$4:X$4000="I",IF(AC$4:AC$4000="DM" ,AE$4:AE$4000,0),0),0))} Thanks for your help!!! -DJW |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |