Why is my IF formula not producing the correct color circles?
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", ""))))
Best Answer

@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.
Answers


@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.

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.

@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.
Help Article Resources
Categories
Check out the Formula Handbook template!