Discussions

Expand all | Collapse all

simple formula

  • 1.  simple formula

    Posted 18 days ago
    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
    ------------------------------


  • 2.  RE: simple formula

    Posted 18 days 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
    ------------------------------