Conditional Format Help

[j5b9721]

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(CO7>CF7,CF7>BY7)) 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...

 

[Sheeloo]

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,"result3","false3")...
or
=IF(cond1,IF(cond2,IF(cond3,...)

You need to take care of else path and matching parenthesis.

 

[Shane Devenshire]

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

 

[j5b9721]

Thank you!!! it worked well!!!

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