Forum Discussion
Conditional Formatting
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).
Thank you for your help with this. The version you supplied of my example sheet has some really good alternative information in here and is very helpful! I appreciate your knowledge and assistance!