This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Leave blank if less than zero

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

0
2341
12

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 25, 2020 11:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 25, 2020 01:37 PM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 25, 2020 01:42 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 25, 2020 02:00 PM

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})
)
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 25, 2020 05:56 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 25, 2020 08:42 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 25, 2020 09:03 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 25, 2020 09:10 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 26, 2020 12:32 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 26, 2020 12:52 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 27, 2020 11:16 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 27, 2020 01:14 PM

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})
)
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Apr 27, 2020 07:06 PM

This works, thanks for your help!