Greater/less than with negative number formula

Question

Hello,I am having trouble figuring out why my formula doesn't work.  I have tried several versions and none have worked so I need help.I have a field that is a % that could be negative or positive.  I need to create a formula that says if that % is -3% or higher that is exceeding, if it is below -4 that is bad.  So, -1% good, -18% bad, 25 % good, etc.This is where I ended up but still not working, it makes everything good or bad, there is no separation.  I even tried separation, -1, -2, -3 out in the formula but still no luck.If([%]>=-3, "Exceeded",If([%]<=-4, "BAD"))or If([%]>=-3, "Exceeded", "Bad")Any help is greatly appreciated.  ------------------------------Jennifer Silberstein------------------------------

MarkShnierYou · Answer

If your field format is set to show as a percent, then the actual stored values are still saved as regular number. So for example 1% is stored as 0.01. I believe that Excel would be the same behaviour.

Try this

IF(
[%] >=-0.03, "Exceeded",
[%]<0.03, "BAD")

If the [%] is blank, then this should calculate to blank if the [%] field is set not to treat blank as zero (if its a data entry field) .

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

Sign up

Login to the community