Conditional Format Help

  • Thread starter Thread starter j5b9721
  • Start date Start date
J

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...
 
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.
 
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
 
Thank you!!! it worked well!!!

Shane Devenshire said:
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
Click to expand...
 
Back
Top