# Leave blank if less than zero

Topic Labels: Formulas
2341 12
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

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),
“”
)
)
)
)
)
)
)
)
)
)
)
)

12 Replies 12  18 - Pluto

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.  18 - Pluto

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.  18 - Pluto

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})
)
``````  5 - Automation Enthusiast

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!  18 - Pluto

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.  5 - Automation Enthusiast

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

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?  5 - Automation Enthusiast

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.   18 - Pluto

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.  5 - Automation Enthusiast

Thanks for this kuovonne, however, I am getting an error message when I insert that code. Is it correct?   18 - Pluto

Sorry, there was a missing comma.

Sometimes I am typing away from my computer and cannot always test out the complete formula.

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

This works, thanks for your help! 