Leave blank if less than zero

Bill_Grinstead · ‎Apr 25, 2020

How can I edit this formula to leave the field blank if the number returned is negative? Thanks!

IF({Update Cycle}=‘1’,({Days Since Update}-30),
IF({Update Cycle}=‘2’,({Days Since Update}-60),
IF({Update Cycle}=‘3’,({Days Since Update}-90),
IF({Update Cycle}=‘4’,({Days Since Update}-120),
IF({Update Cycle}=‘5’,({Days Since Update}-150),
IF({Update Cycle}=‘6’,({Days Since Update}-180),
IF({Update Cycle}=‘7’,({Days Since Update}-210),
IF({Update Cycle}=‘8’,({Days Since Update}-240),
IF({Update Cycle}=‘9’,({Days Since Update}-270),
IF({Update Cycle}=‘10’,({Days Since Update}-300),
IF({Update Cycle}=‘11’,({Days Since Update}-330),
IF({Update Cycle}=‘12’,({Days Since Update}-360),
“”
)
)
)
)
)
)
)
)
)
)
)
)

ScottWorld · ‎Apr 25, 2020

There might be other ways to do this, but I would just do it as 2 different formula fields.

What you outlined above would give you the result in your 1st formula field.

Then, in your 2nd formula field, you would evaluate whether your 1st formula field was negative or positive. If it was negative, it would result in a blank field. If it was positive, it would show you the same result.

ScottWorld · ‎Apr 25, 2020

The other way you could do it would be to wrap that entire large block of IF statements in yet another enclosing IF statement, which would essentially be evaluating the results of your large block of IF statements as the result to evaluate for the one “master surrounding IF statement”. So just put another IF statement AROUND your existing block of IF statements.

kuovonne · ‎Apr 25, 2020

To have a blank field instead of a negative numbers, wrap the formula in an IF statement. You can also simplify your formula using a little math so that you do not need to nest so many IF statements.

IF(
  ({Days Since Update} - (30 * {Update Cycle})) > 0,
   {Days Since Update} - (30 * {Update Cycle})
)

Bill_Grinstead · ‎Apr 25, 2020

I’d like to use this simplified method, but i input what you wrote and it gave an error. Sorry, I’m a noob. I could do it either way, but I’m not even sure how to wrap the entire thing in an IF statement as suggested. Could anyone show me how that’s done on the original formula? Thanks!

kuovonne · ‎Apr 25, 2020

Are {Days Since Update} and {Update Cycle} both number fields, or are they some other type of field?

If they are not numbers, their values need to be converted to numbers in order to do math with them. Here are two options:

Change the field type to number, or
Wrap the field names in the VALUE() function in the formula field.

VALUE({Days Since Update}}
VALUE({Update Cycle})

The formula that I provided already has the IF statement to avoid displaying negative values.

The support page for formulas has more information on how formulas work, including using the IF statement and doing basic calculations.

Bill_Grinstead · ‎Apr 25, 2020

Ok so I have changed the field type to Number, but not sure what formula to put in place of my original formula.

kuovonne · ‎Apr 25, 2020

Now that the field types are numbers, do you still get the error message with my first formula?

If so, can you provide a screen capture that shows all three fields?

Bill_Grinstead · ‎Apr 26, 2020

It’s working, thanks! Now one last thing, how can I make it leave the cell blank if my Update Cycle cell is empty? Right now it shows whatever number is in the Days Since Update cell if that cell is empty.

kuovonne · ‎Apr 26, 2020

You can use the AND function to combine conditions for the IF function:

IF(
  AND(
    NOT({Days Since Update} = BLANK()),
    NOT({Update Cycle} = BLANK()),
    ({Days Since Update} - (30 * {Update Cycle})) > 0
  )
 {Days Since Update} - (30 * {Update Cycle})
)

Normally you can use just the {field name} in the condition, but because these are numeric fields, you need the NOT({field name} = BLANK()} syntax.