Why is my IF formula not producing the correct color circles?

smbrown929

Here is the formula I am using and every time I adjust it the formula always returns a red circle no matter what I change:

=IF([Profit Margin]@row < "30.00%", "Red", IF(AND([Profit Margin]@row = "30.00%"), "Yellow", "Green"))

I have also tried this formula: =IF([Profit Margin]@row < "30.00%", "Red", (IF(AND([Profit Margin]@row = "30.00%"), "Yellow", IF(AND([Profit Margin]@row > "30.00%"), "Green", ""))))

Lucas Rayala

@smbrown929, when you add quotes around the percentage, you turn it into a word (vs a number). Try this:

=IF([Profit Margin]@row < 0.3, "Red", IF(AND([Profit Margin]@row = 0.03), "Yellow", "Green"))

Numbers don't need/shouldn't have quotes around them.

smbrown929

Lucas Rayala

@smbrown929, when you add quotes around the percentage, you turn it into a word (vs a number). Try this:

=IF([Profit Margin]@row < 0.3, "Red", IF(AND([Profit Margin]@row = 0.03), "Yellow", "Green"))

Numbers don't need/shouldn't have quotes around them.

smbrown929

Thank you Lucas that worked perfectly and thanks for the additional info about the quote turning it into a word I did not know that.

Lucas Rayala

@smbrown929 no problem! This factoid about numbers vs words is true for Excel. It's actually pretty much universally true for all software that uses formulas as well as all programming languages. Words are referred to as "strings" (no idea why) and treated separately from numbers.