CF help please

[j5b9721]

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(DW6X6,EC6)<=-5%,SUM(DV6X6,EC6)<=-5%),OR(MAX(DT6W6)>4.5%,MAX(DP6,DY6,EH6)>-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6X6,EC6)<=-5%))))

When copied down the page (at line 100) it changed to this...
=EC101="" ""
OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100X100,EC100)<=-5%,SUM(DV100X100,EC100)<=-5%)


=IF(CE7="","", (command is needed... as it activates early)
AND(OR(
EC6<=-5%,
SUM(DX6,EC6)<=-5%,
SUM(DW6X6,EC6)<=-5%,
SUM(DV6X6,EC6)<=-5%), (one must be true)

OR(MAX(DT6W6)>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(DW6X6,EC6)<=-5%)))) (one must be true)

 

[Simon Lloyd]

Whats wrong with the statement changing to EC100...etc do you want the
to always be DX6 etc?, other than that i cant see what you are gettin
at

In CF I have this string, but its format to long to copy down the page
I
there a way I can shorten this statement

=IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6X6,EC6)<=-5%,SUM(DV6X6,EC6)<=-5%),OR(MAX(DT6W6)>4.5%,MAX(DP6,DY6,EH6)>-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6X6,EC6)<=-5%)))

When copied down the page (at line 100) it changed to this..
=EC101="" "
OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100X100,EC100)<=-5%,SUM(DV100X100,EC100)<=-5%


=IF(CE7="","", (command is needed... as it activates early
AND(OR
EC6<=-5%
SUM(DX6,EC6)<=-5%
SUM(DW6X6,EC6)<=-5%
SUM(DV6X6,EC6)<=-5%), (one must be true

OR(MAX(DT6W6)>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(DW6X6,EC6)<=-5%)))) (one must be true

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[j5b9721]

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(DW6X6,EC6)<=-5%,SUM(DV6X6,EC6)<=-5%),OR(MAX(DT6W6)>4.5%,MAX(DP6,DY6,EH6)>-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6X6,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(DW100X100,EC100)<=-5%,SUM(DV100X100,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.

 

[j5b9721]

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]

I have just copied your formula in to EC6 (in xl2007) and copied down t
row 300 and this is the result
=IF(CE301="","",AND(OR(EC300<=-5%,SUM(DX300,EC300)<=-5%,SUM(DW300X300,EC300)<=-5%,SUM(DV300X300,EC300)<=-5%),OR(MAX(DT300W300)>4.5%,MAX(DP300,DY300,EH300)>-3.5%),AND(EC300<1%,OR(EC300<-5%,SUM(DX300)<=-5%,SUM(DX300,EC300)<=-5%,SUM(DW300X300,EC300)<=-5%)))

Is this correct

Please Note: that cell ce6 has the conditional formula, and cell EC7 i
fo
daily data log. So ce6 is coppied into ce8, ce10, ce12, ect... (dow
th
page
But it it also does not matter how its coppied down the page. I trie
copping the cell directly from ce6 to cells below ce100 having the sam
error. Its Very Strange




Office Discussion' (http://www.thecodecage.com)

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[j5b9721]

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.

 

[j5b9721]

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]

In CF I have this string, but its format to long to copy down the page
I
there a way I can shorten this statement

=IF(CE7="","",AND(OR(EC6<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6X6,EC6)<=-5%,SUM(DV6X6,EC6)<=-5%),OR(MAX(DT6W6)>4.5%,MAX(DP6,DY6,EH6)>-3.5%),AND(EC6<1%,OR(EC6<-5%,SUM(DX6)<=-5%,SUM(DX6,EC6)<=-5%,SUM(DW6X6,EC6)<=-5%)))

When copied down the page (at line 100) it changed to this..
=EC101="" "
OR(EC100<=-5%,SUM(DX100,EC100)<=-5%,SUM(DW100X100,EC100)<=-5%,SUM(DV100X100,EC100)<=-5% Looking at thi
=IF(CE7="","", (command is needed... as it activates early
AND(OR
EC6<=-5%
SUM(DX6,EC6)<=-5%
SUM(DW6X6,EC6)<=-5%
SUM(DV6X6,EC6)<=-5%), (one must be true)it seems that you can simply test for SUM(DV6X6,EC6)<=-5%) as you ar

checking for any of the 3 conditions, if DV6 was empty or the sum wa
not <=-5% then DW6X6...etc would be true and so on

OR(MAX(DT6W6)>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(DW6X6,EC6)<=-5%)))) (one must be true)You can do the same for the above portion of the formula

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com