Forum Discussion

JenniferSilbers's avatar
JenniferSilbers
Qrew Trainee
4 years ago

Greater/less than with negative number formula

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
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    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
    ------------------------------