Using a Formua to Set Conditional Formatting with Several Options

[jhvolfan]

I am trying to set conditional formatting using a formula. The problem is, the cell I am formatting already has a formula in it (it pulls from another sheet and displays the value in the other sheet). What I want the conditional formatting to is: if n4/x4 is equal to or less than 50%, make the value red. if n4/x4 is equal to 51% but less than 76%, make the value orange, if n4/x4 is equal to 76% but less than 91%, make the value yellow. If n4/x4 is equal to 91% but less than 101%, make the value green. The problem I am having is getting the formula to get the correct result so I can have the value (and the value must be expressed in the numerical value that is pulled forward by the original formula) be a certain color based on its (the value's) percentage of the total. Every formula I tried so far doesn't work - I don't get any formatting and it doesn't look like it is doing anything. I even tried the current formula and then did an "and" if statement and that isn't working either. If I can get the first one set, then all the other cells that need the formula/conditional formatting will be a matter of copy/paste. Basically, if my formula (=sumif(sheet1!$b$5:$b$270,$m4,Sheet1!$d$5:$d$270) pulls forward a value of 2 and 2 is 1.19% of the total (168), then the 2 needs to be red. However, in cell q4 where the % of total would be based on the sum of cells n4-q4 (119), then the value in q4 (44) needs to be orange (because 119 is 70.83% of 168) and 70.83% is greater than 51% but less than 76%. Any help is greatly appreciated. I've gone thru the Microsoft StepbyStep book and searched conditional formatting on this forum and did not find anything that would help me figure out the problem. Sincerely - Julie

 

[BobUmlas]

4 conditional formatting formulas. Mark each one a Stop if True:
=$N4/$X4<1.01 and make Green
=$N4/$X4<0.91 and make yellow
=$N4/$X4<0.76 and make orange
=$N4/$X4<0.5 and make red

 

[jhvolfan]

4 conditional formatting formulas. Mark each one a Stop if True:
=$N4/$X4<1.01 and make Green
=$N4/$X4<0.91 and make yellow
=$N4/$X4<0.76 and make orange
=$N4/$X4<0.5 and make red

Thank you, I think that will work. I did think I could just copy/paste and then "add the math" but that doesn't seem to be working. For each cell I've had to re-enter the conditional formatting rules. Did I miss something?

 

[jhvolfan]

Those worked great and I was able to make necessary adjustments for different lines. I appreciate the help. I've now run into another issue. I'm trying to do basically the same thing except the formula needs to be a range. For example, =$H4/$H$23<4.99 and make green, =$H4/$H$23<5-9.99 and make yellow, $H4/$H$23<10-14.99 and make orange, $H4/$H$23>15 and make red. Every version I've tried, I am only getting one color - not the green/yellow/orange/red I'm attempting. What am I missing to make this work? Can I use the word "to"?

 

[jhvolfan]

I think I need an if function but am not sure how to get it set right.