Forum Discussion

MaryPerkins's avatar
MaryPerkins
Qrew Trainee
4 years ago

simple formula

Hey All,

I've a formula Numeric Formula field that calculates Units Remaining. 

[Weekly Units]-[# Units Used]

This works. Now I'm trying to create a message if this field goes NEGATIVE. 

if (([Weekly Units]-[# Units Used])<0, ToText("TOO MANY UNITS USED, TRY AGAIN"),
if (([weekly units]=[# units used]),"zero",
([weekly units]-[# units used]))))

I'm receiving a Formula Syntax Error for the last line: "Expecting Text but found Number". If this is a formula NUMERIC field, why am I getting this message?

Here are other variations that I've tried for that last  line, but still get the same message:

(ToNumber([weekly units])-ToNumber([# units used]))))

(ToNumber([weekly units]-[# units used]))))

Thanks in advance! Mary


------------------------------
Mary Perkins
M3TR1CS Business Solutions
mary.perkins@m3tr1cs.com
------------------------------
Formulas and functions

1 Reply

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    4 years ago
    try this as a formula text field.  I will use a formula variable to make the formula more tidy.
    Also, btw, unlike excel you normally do  not need to nest your IFs and you can avoid going cross eyed tryin to match up the lining up the parentheses.


    var number UnitsRemaining = . [Weekly Units]-[# Units Used];

    IF(
    $UnitsRemaining < 0, "TOO MANY UNITS USED, TRY AGAIN",
    $UnitsRemaining = 0, "zero",
    ToText($UnitsRemaining))


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------