Forum Discussion

JenniferSilbers's avatar
Qrew Trainee
3 years ago

Greater/less than with negative number formula


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

1 Reply

  • 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

    [%] >=-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)