simple formula

Question

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 Solutionsmary.perkins@m3tr1cs.com------------------------------

MarkShnierYou · Answer

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
------------------------------

Sign up

Login to the community