Forum Discussion
Solved
Conditional Formatting
Is it possible to utilize conditional formatting rules on a cell range where the values are being calculated with formulas?
Problem #1: The formula in C2:C15
=IF(B2<B23,"1",IF(AND(B2>=B23,B2<C23),"2",IF(AND(B2>=C23,B2<D23),"3",IF(AND(B2>=D23,B2<E23),"4",IF(B2>E23,"5")))))
returns text values, not numbers, because you enclose 1 etc. in quotes: "1". Change it it to
=IF(B2<B23,1,IF(AND(B2>=B23,B2<C23),2,IF(AND(B2>=C23,B2<D23),3,IF(AND(B2>=D23,B2<E23),4,IF(B2>E23,5)))))
Many of the conditions are superfluous, you can shorten the formula to
=IF(B2<B23,1,IF(B2<C23,2,IF(B2<D23,3,IF(B2<E23,4,5))))
Problem #2: the conditional formatting formula should be
=$C2=5
and similar for the other values. See the attached version (I used a different color for 2, but you can change that).
Yes. In fact, it works just the same as for a cell range containing constant values.
Perhaps I am not utilizing conditional formatting correctly then because I am striking out on how to make it work. Using the attached file can you help me format the rating in cells C2:C15 & A2:A15 to highlight according to the colors in cells H5:H9?
Problem #1: The formula in C2:C15
=IF(B2<B23,"1",IF(AND(B2>=B23,B2<C23),"2",IF(AND(B2>=C23,B2<D23),"3",IF(AND(B2>=D23,B2<E23),"4",IF(B2>E23,"5")))))
returns text values, not numbers, because you enclose 1 etc. in quotes: "1". Change it it to
=IF(B2<B23,1,IF(AND(B2>=B23,B2<C23),2,IF(AND(B2>=C23,B2<D23),3,IF(AND(B2>=D23,B2<E23),4,IF(B2>E23,5)))))
Many of the conditions are superfluous, you can shorten the formula to
=IF(B2<B23,1,IF(B2<C23,2,IF(B2<D23,3,IF(B2<E23,4,5))))
Problem #2: the conditional formatting formula should be
=$C2=5
and similar for the other values. See the attached version (I used a different color for 2, but you can change that).
