Hi, I am trying to conditionally format cells to fill blue when certain conditions are met following an "IF" statement.

In column A1 I will have a letter. In column B1 I want a number to represent that letter according to the number of the letter of the alphabet. So if A1 reads "A", B1 reads "1" and so on. That part I worked out fine by using "IF" formulas repeatedly up until Z in A1 = "26" in B1. What I want to do afterward is format B1 to turn blue if a number is less than 14. I know how to do it, and it works fine if just the number itself is there. But it does not work in a cell where a formula is used to result in the number. The cell just stays white when I apply the conditional format to turn blue. What am I doing wrong? Or can this just not be done when the cell contains a formula?

Hi,

As far as I know, the issue may be caused by the result cells not a number format. Please use the ISNUMBER formula to check them.

I also tested to use conditional formatting to color the cell which inculde a formula. It worked fine.

Thus, if the issue still exists,please upload your "IF" statement, I want to test.

Regards,

George Zhao
TechNet Community Support

The ISNUMBER test returned "false", even when the cell is formatted as a number. I can't use it as a number in any capacity including sums or averages. Anyway, here is the formula. It's long.

=IF(G3="DISC","DISC",IF(G3="N/A","N/A",IF(G3="a","1",IF(G3="b","2",IF(G3="ng","NG",IF(G3="c","3",IF(G3="d","4",IF(G3="e","5",IF(G3="f","6",IF(G3="g","7",IF(G3="h","8",IF(G3="i","9",IF(G3="j","10",IF(G3="k","11",IF(G3="l","12",IF(G3="m","13",IF(G3="n","14",IF(G3="o","15",IF(G3="p","16",IF(G3="q","17",IF(G3="r","18",IF(G3="s","19",IF(G3="t","20",IF(G3="u","21",IF(G3="v","22",IF(G3="w","23",IF(G3="x","24",IF(G3="y","25",IF(G3="z","26"," ")))))))))))))))))))))))))))))

Basically I want the letter grade to return a number unless other conditions apply. Thanks for your help. From there I may want to use conditional formatting on the cells or find an average of the numbers.

• Edited by BarryB1124 20 hours 41 minutes ago

I took out the quotation marks for the numbers and now the formats work. Sorry to waste your time.