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).
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!