| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
CF help please
In CF I have this string, but its format to long to copy down the page. Is
there a way I can shorten this statement? =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When copied down the page (at line 100) it changed to this... =EC101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) =IF(CE7="","", (command is needed... as it activates early) AND(OR( EC6<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%, SUM(DV6:DX6,EC6)<=-5%), (one must be true) OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%), (must be true) AND(EC6<1%, (must be true) OR(EC6<-5%, SUM(DX6)<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%)))) (one must be true) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CF help please
Whats wrong with the statement changing to EC100...etc do you want them to always be DX6 etc?, other than that i cant see what you are getting at? j5b9721;433004 Wrote: In CF I have this string, but its format to long to copy down the page. Is there a way I can shorten this statement? =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When copied down the page (at line 100) it changed to this... =EC101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) =IF(CE7="","", (command is needed... as it activates early) AND(OR( EC6<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%, SUM(DV6:DX6,EC6)<=-5%), (one must be true) OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%), (must be true) AND(EC6<1%, (must be true) OR(EC6<-5%, SUM(DX6)<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%)))) (one must be true) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120233 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CF help please
The problem that I have is the formula is copied into cell EC6 as a
"Conditional Format - Condition 1" as this.... =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When the cell is copied down the page (even a new blank page) it changes from that formula to this, at EC100,EC101, ect... (to this error) =CE101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) I have tried it on different versions of excel also... excel 2000 win xp, excel 2007 Vista, with the same affect taking place when its coppied below line 100. I can only assume the formula string is to long. "Simon Lloyd" wrote: Whats wrong with the statement changing to EC100...etc do you want them to always be DX6 etc?, other than that i cant see what you are getting at? j5b9721;433004 Wrote: In CF I have this string, but its format to long to copy down the page. Is there a way I can shorten this statement? =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When copied down the page (at line 100) it changed to this... =EC101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) =IF(CE7="","", (command is needed... as it activates early) AND(OR( EC6<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%, SUM(DV6:DX6,EC6)<=-5%), (one must be true) OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%), (must be true) AND(EC6<1%, (must be true) OR(EC6<-5%, SUM(DX6)<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%)))) (one must be true) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120233 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CF help please
Please Note: that cell ce6 has the conditional formula, and cell EC7 is for
daily data log. So ce6 is coppied into ce8, ce10, ce12, ect... (down the page) But it it also does not matter how its coppied down the page. I tried copping the cell directly from ce6 to cells below ce100 having the same error. Its Very Strange! "Simon Lloyd" wrote: Whats wrong with the statement changing to EC100...etc do you want them to always be DX6 etc?, other than that i cant see what you are getting at? j5b9721;433004 Wrote: In CF I have this string, but its format to long to copy down the page. Is there a way I can shorten this statement? =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When copied down the page (at line 100) it changed to this... =EC101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) =IF(CE7="","", (command is needed... as it activates early) AND(OR( EC6<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%, SUM(DV6:DX6,EC6)<=-5%), (one must be true) OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%), (must be true) AND(EC6<1%, (must be true) OR(EC6<-5%, SUM(DX6)<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%)))) (one must be true) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120233 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CF help please
I have just copied your formula in to EC6 (in xl2007) and copied down to row 300 and this is the result =IF(CE301="","",AND(OR(EC300<=-5%,SUM(DX300,EC300)<=-5%,SUM(DW300:DX300,EC300)<=-5%,SUM(DV300:DX300,EC300)<=-5%),OR(MAX(DT300:DW300)4.5%,MAX(DP300,DY300,EH300 )-3.5%),AND(EC300<1%,OR(EC300<-5%,SUM(DX300)<=-5%,SUM(DX300,EC300)<=-5%,SUM(DW300:DX300,EC300)<=-5%)))) Is this correct? j5b9721;434740 Wrote: Please Note: that cell ce6 has the conditional formula, and cell EC7 is for daily data log. So ce6 is coppied into ce8, ce10, ce12, ect... (down the page) But it it also does not matter how its coppied down the page. I tried copping the cell directly from ce6 to cells below ce100 having the same error. Its Very Strange! "Simon Lloyd" wrote: Whats wrong with the statement changing to EC100...etc do you want them to always be DX6 etc?, other than that i cant see what you are getting at? j5b9721;433004 Wrote: In CF I have this string, but its format to long to copy down the page. Is there a way I can shorten this statement? =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When copied down the page (at line 100) it changed to this... =EC101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) =IF(CE7="","", (command is needed... as it activates early) AND(OR( EC6<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%, SUM(DV6:DX6,EC6)<=-5%), (one must be true) OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%), (must be true) AND(EC6<1%, (must be true) OR(EC6<-5%, SUM(DX6)<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%)))) (one must be true) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'CF help please - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=120233) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120233 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CF help please
well it is right... Just copying the formula into a cell does work for me
also. Did you copy into "conditional format - condition 1"??? If "yes" I may have to try again reloading the software and try it again, but trying this on 2 different computers I dont why its happening to me. "Simon Lloyd" wrote: I have just copied your formula in to EC6 (in xl2007) and copied down to row 300 and this is the result =IF(CE301="","",AND(OR(EC300<=-5%,SUM(DX300,EC300)<=-5%,SUM(DW300:DX300,EC300)<=-5%,SUM(DV300:DX300,EC300)<=-5%),OR(MAX(DT300:DW300)4.5%,MAX(DP300,DY300,EH300 )-3.5%),AND(EC300<1%,OR(EC300<-5%,SUM(DX300)<=-5%,SUM(DX300,EC300)<=-5%,SUM(DW300:DX300,EC300)<=-5%)))) Is this correct? j5b9721;434740 Wrote: Please Note: that cell ce6 has the conditional formula, and cell EC7 is for daily data log. So ce6 is coppied into ce8, ce10, ce12, ect... (down the page) But it it also does not matter how its coppied down the page. I tried copping the cell directly from ce6 to cells below ce100 having the same error. Its Very Strange! "Simon Lloyd" wrote: Whats wrong with the statement changing to EC100...etc do you want them to always be DX6 etc?, other than that i cant see what you are getting at? j5b9721;433004 Wrote: In CF I have this string, but its format to long to copy down the page. Is there a way I can shorten this statement? =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When copied down the page (at line 100) it changed to this... =EC101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) =IF(CE7="","", (command is needed... as it activates early) AND(OR( EC6<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%, SUM(DV6:DX6,EC6)<=-5%), (one must be true) OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%), (must be true) AND(EC6<1%, (must be true) OR(EC6<-5%, SUM(DX6)<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%)))) (one must be true) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'CF help please - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=120233) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120233 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CF help please
I reloaded my software... and have the same problem again in Conditional
Format -Condition 1 . But this time I also tried to copy your string into cell CE300 as Conditional Format - Condition 1 selecting the "Formula Is" section, but it would not paste into the Condition 1 box at all. Thats why I think the string is to long, and needs to be shorter. "Simon Lloyd" wrote: I have just copied your formula in to EC6 (in xl2007) and copied down to row 300 and this is the result =IF(CE301="","",AND(OR(EC300<=-5%,SUM(DX300,EC300)<=-5%,SUM(DW300:DX300,EC300)<=-5%,SUM(DV300:DX300,EC300)<=-5%),OR(MAX(DT300:DW300)4.5%,MAX(DP300,DY300,EH300 )-3.5%),AND(EC300<1%,OR(EC300<-5%,SUM(DX300)<=-5%,SUM(DX300,EC300)<=-5%,SUM(DW300:DX300,EC300)<=-5%)))) Is this correct? j5b9721;434740 Wrote: Please Note: that cell ce6 has the conditional formula, and cell EC7 is for daily data log. So ce6 is coppied into ce8, ce10, ce12, ect... (down the page) But it it also does not matter how its coppied down the page. I tried copping the cell directly from ce6 to cells below ce100 having the same error. Its Very Strange! "Simon Lloyd" wrote: Whats wrong with the statement changing to EC100...etc do you want them to always be DX6 etc?, other than that i cant see what you are getting at? j5b9721;433004 Wrote: In CF I have this string, but its format to long to copy down the page. Is there a way I can shorten this statement? =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When copied down the page (at line 100) it changed to this... =EC101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) =IF(CE7="","", (command is needed... as it activates early) AND(OR( EC6<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%, SUM(DV6:DX6,EC6)<=-5%), (one must be true) OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%), (must be true) AND(EC6<1%, (must be true) OR(EC6<-5%, SUM(DX6)<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%)))) (one must be true) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'CF help please - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=120233) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120233 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
CF help please
j5b9721;433004 Wrote: In CF I have this string, but its format to long to copy down the page. Is there a way I can shorten this statement? =IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%,SUM(DV6:DX6,EC6)<=-5%),OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6:DX6,EC6)<=-5%)))) When copied down the page (at line 100) it changed to this... =EC101="" "" OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100:DX100,EC100)<=-5%,SUM(DV100:DX100,EC100)<=-5%) Looking at this =IF(CE7="","", (command is needed... as it activates early) AND(OR( EC6<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%, SUM(DV6:DX6,EC6)<=-5%), (one must be true)it seems that you can simply test for SUM(DV6:DX6,EC6)<=-5%) as you are checking for any of the 3 conditions, if DV6 was empty or the sum was not <=-5% then DW6:DX6...etc would be true and so on. OR(MAX(DT6:DW6)4.5%,MAX(DP6,DY6,EH6)-3.5%), (must be true) AND(EC6<1%, (must be true) OR(EC6<-5%, SUM(DX6)<=-5%, SUM(DX6,EC6)<=-5%, SUM(DW6:DX6,EC6)<=-5%)))) (one must be true)You can do the same for the above portion of the formula. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=120233 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
