Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Help
I do understand a little about the AND formula, but my problem goes deeper
then that from what I tried to do today. I hope you will be able to help me solve this problem as it's becoming very complicated and taxing to me also. If you have time I will appreciate your help. It seams that I may need in Conditional Format a multi-situation in 2 Conditions. I will go straight to the problem... you just may follow whats happening with my explination. BZ CA CB CC CD 221 19 -65 74 --- line 4 -0.53% 1.78% -1.75% 5.89% --- line 6 Formula is =IF(CC7="","",(CC7-CB7)/CB7) is "CC6" It needs CF "Condition 1" has a few given situations that are need. Condition 1 needs... (red text & yellow fill) CC6 to be between 0% and -4.9% "TRUE" If CC6<-5%, BY6 ... CB6 and if any is =CC6 "TRUE" If CC6<-5%, Sum(BY6:CB6) must be 4.75% "TRUE" If CC6<-5%, BY4 ... CB4 all must be <60 "TRUE" I have tried some different ways to write this condition... but at times it format the cell before I log a price in the cell. Can you also help me with "Condition 2"??? I dont know if this is right... =IF(CO7="","",AND(CO7CF7,CF7BY7)) I need to add more "True" logical situations to this also... but where do I put them? But I need to start with this =IF(CO7="","", to stop the formating early like in the above Condition 1. How do I add more logical situations to this??? Thanks for your time... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Help
if cond1, cond2, cond3 all have to be true and you want to test them only if
cell is not blank then you can use (for the second situation) =IF(AND(CO7<"",cond1,cond2,cond3),"ALL True","At least one false)... If you want IF.. ELSE IF... ELSE IF type of situation then use nested IFs (max 7 in Excel 2003) =IF(Cond1,"result",IF(Cond2,"result2",IF(Cond3,"re sult3","false3")... or =IF(cond1,IF(cond2,IF(cond3,...) You need to take care of else path and matching parenthesis. "j5b9721" wrote: I do understand a little about the AND formula, but my problem goes deeper then that from what I tried to do today. I hope you will be able to help me solve this problem as it's becoming very complicated and taxing to me also. If you have time I will appreciate your help. It seams that I may need in Conditional Format a multi-situation in 2 Conditions. I will go straight to the problem... you just may follow whats happening with my explination. BZ CA CB CC CD 221 19 -65 74 --- line 4 -0.53% 1.78% -1.75% 5.89% --- line 6 Formula is =IF(CC7="","",(CC7-CB7)/CB7) is "CC6" It needs CF "Condition 1" has a few given situations that are need. Condition 1 needs... (red text & yellow fill) CC6 to be between 0% and -4.9% "TRUE" If CC6<-5%, BY6 ... CB6 and if any is =CC6 "TRUE" If CC6<-5%, Sum(BY6:CB6) must be 4.75% "TRUE" If CC6<-5%, BY4 ... CB4 all must be <60 "TRUE" I have tried some different ways to write this condition... but at times it format the cell before I log a price in the cell. Can you also help me with "Condition 2"??? I dont know if this is right... =IF(CO7="","",AND(CO7CF7,CF7BY7)) I need to add more "True" logical situations to this also... but where do I put them? But I need to start with this =IF(CO7="","", to stop the formating early like in the above Condition 1. How do I add more logical situations to this??? Thanks for your time... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Help
Hi,
If you want the same format for any of these 4 conditions than you only need one formula: In 2003: 1. Select the cells you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(AND($CC$6=0,$CC$6<-4.9%),AND($CC$6<-5%,OR($BY$6:$CB$6=$CC$6)),AND($CC$6<-5%,SUM($BY$6:$CB$6)4.75%),AND(CC6<-5%,BY4:CB4<60)) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =OR(AND($CC$6=0,$CC$6<-4.9%),AND($CC$6<-5%,OR($BY$6:$CB$6=$CC$6)),AND($CC$6<-5%,SUM($BY$6:$CB$6)4.75%),AND(CC6<-5%,BY4:CB4<60)) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "j5b9721" wrote: I do understand a little about the AND formula, but my problem goes deeper then that from what I tried to do today. I hope you will be able to help me solve this problem as it's becoming very complicated and taxing to me also. If you have time I will appreciate your help. It seams that I may need in Conditional Format a multi-situation in 2 Conditions. I will go straight to the problem... you just may follow whats happening with my explination. BZ CA CB CC CD 221 19 -65 74 --- line 4 -0.53% 1.78% -1.75% 5.89% --- line 6 Formula is =IF(CC7="","",(CC7-CB7)/CB7) is "CC6" It needs CF "Condition 1" has a few given situations that are need. Condition 1 needs... (red text & yellow fill) CC6 to be between 0% and -4.9% "TRUE" If CC6<-5%, BY6 ... CB6 and if any is =CC6 "TRUE" If CC6<-5%, Sum(BY6:CB6) must be 4.75% "TRUE" If CC6<-5%, BY4 ... CB4 all must be <60 "TRUE" I have tried some different ways to write this condition... but at times it format the cell before I log a price in the cell. Can you also help me with "Condition 2"??? I dont know if this is right... =IF(CO7="","",AND(CO7CF7,CF7BY7)) I need to add more "True" logical situations to this also... but where do I put them? But I need to start with this =IF(CO7="","", to stop the formating early like in the above Condition 1. How do I add more logical situations to this??? Thanks for your time... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Help
Thank you!!! it worked well!!!
"Shane Devenshire" wrote: Hi, If you want the same format for any of these 4 conditions than you only need one formula: In 2003: 1. Select the cells you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =OR(AND($CC$6=0,$CC$6<-4.9%),AND($CC$6<-5%,OR($BY$6:$CB$6=$CC$6)),AND($CC$6<-5%,SUM($BY$6:$CB$6)4.75%),AND(CC6<-5%,BY4:CB4<60)) 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =OR(AND($CC$6=0,$CC$6<-4.9%),AND($CC$6<-5%,OR($BY$6:$CB$6=$CC$6)),AND($CC$6<-5%,SUM($BY$6:$CB$6)4.75%),AND(CC6<-5%,BY4:CB4<60)) 5. Click the Format button and choose a format. 6. Click OK twice -- If this helps, please click the Yes button. Cheers, Shane Devenshire "j5b9721" wrote: I do understand a little about the AND formula, but my problem goes deeper then that from what I tried to do today. I hope you will be able to help me solve this problem as it's becoming very complicated and taxing to me also. If you have time I will appreciate your help. It seams that I may need in Conditional Format a multi-situation in 2 Conditions. I will go straight to the problem... you just may follow whats happening with my explination. BZ CA CB CC CD 221 19 -65 74 --- line 4 -0.53% 1.78% -1.75% 5.89% --- line 6 Formula is =IF(CC7="","",(CC7-CB7)/CB7) is "CC6" It needs CF "Condition 1" has a few given situations that are need. Condition 1 needs... (red text & yellow fill) CC6 to be between 0% and -4.9% "TRUE" If CC6<-5%, BY6 ... CB6 and if any is =CC6 "TRUE" If CC6<-5%, Sum(BY6:CB6) must be 4.75% "TRUE" If CC6<-5%, BY4 ... CB4 all must be <60 "TRUE" I have tried some different ways to write this condition... but at times it format the cell before I log a price in the cell. Can you also help me with "Condition 2"??? I dont know if this is right... =IF(CO7="","",AND(CO7CF7,CF7BY7)) I need to add more "True" logical situations to this also... but where do I put them? But I need to start with this =IF(CO7="","", to stop the formating early like in the above Condition 1. How do I add more logical situations to this??? Thanks for your time... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Help
Hi Sheeloo...
Thank you for the info about that situation I been using it in more ways too!!! Do you think that you help me with this??? I need to have a cell (A1) CF condition 1 added to another cells (B1) CF condition 2 but include more on the formula also... How would I write that???? "Sheeloo" wrote: if cond1, cond2, cond3 all have to be true and you want to test them only if cell is not blank then you can use (for the second situation) =IF(AND(CO7<"",cond1,cond2,cond3),"ALL True","At least one false)... If you want IF.. ELSE IF... ELSE IF type of situation then use nested IFs (max 7 in Excel 2003) =IF(Cond1,"result",IF(Cond2,"result2",IF(Cond3,"re sult3","false3")... or =IF(cond1,IF(cond2,IF(cond3,...) You need to take care of else path and matching parenthesis. "j5b9721" wrote: I do understand a little about the AND formula, but my problem goes deeper then that from what I tried to do today. I hope you will be able to help me solve this problem as it's becoming very complicated and taxing to me also. If you have time I will appreciate your help. It seams that I may need in Conditional Format a multi-situation in 2 Conditions. I will go straight to the problem... you just may follow whats happening with my explination. BZ CA CB CC CD 221 19 -65 74 --- line 4 -0.53% 1.78% -1.75% 5.89% --- line 6 Formula is =IF(CC7="","",(CC7-CB7)/CB7) is "CC6" It needs CF "Condition 1" has a few given situations that are need. Condition 1 needs... (red text & yellow fill) CC6 to be between 0% and -4.9% "TRUE" If CC6<-5%, BY6 ... CB6 and if any is =CC6 "TRUE" If CC6<-5%, Sum(BY6:CB6) must be 4.75% "TRUE" If CC6<-5%, BY4 ... CB4 all must be <60 "TRUE" I have tried some different ways to write this condition... but at times it format the cell before I log a price in the cell. Can you also help me with "Condition 2"??? I dont know if this is right... =IF(CO7="","",AND(CO7CF7,CF7BY7)) I need to add more "True" logical situations to this also... but where do I put them? But I need to start with this =IF(CO7="","", to stop the formating early like in the above Condition 1. How do I add more logical situations to this??? Thanks for your time... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy conditional format without using format painter | Excel Worksheet Functions | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
copy conditional format to regular format | Setting up and Configuration of Excel |