Formula troubleshooting - numbers are not working

[haileyfleury]

Hi, 

I have been trying to create a status field that uses a formula to indicate when a project is completed 

IF({% Complete}=100%,"complete"). The problem is whenever I type a number into the formula, it says "invalid formula". What am i doing wrong? I even have copied and pasted formulas from the airtable help page but the number portion is always red, as shown below. The red indicates (to me) that this is where the code is broken, but maybe im wrong? How can i fix it?

 

 

[Ben_Young1]

Hey @haileyfleury!

There's a couple ways to write this formula, but the most direct, valid version of the formula you've provided in your screenshot is something like this:

IF(
    {task assigned?},
    'in progress',
    IF(
        {% Complete} = 100,
        'complete'
    )
)

There are two issues with the formula you provided that need to be fixed in order for the formula to be valid. The first issue is that your formula is made up of two separate IF() functions. These functions' returned values need to be either joined or nested into a single parent IF() function.

The formula I provided you is an example of a nested structure.

The second issue is that you've added a '%' character to the back of your evaluation parameter for your second IF() function. While Airtable might format a field as a percentage, the value is actually just a number (integer). Because of this, Airtable's formula runtime sees the '%' as an invalid character.
You can test this by adding a '%' character to the formula I provided. Airtable will throw an error in response.
Removing the character will resolve this issue.

An alternative structure to the formula I provided would be to join two separate IF() functions. This is more along the lines of the formula you provided, but there are potential issues with using this structure.

IF(
    {task assigned?},
    'in progress'
)
&
IF(
    {% Complete} = 100,
    'complete'
)

When using this structure, it's very likely that you will see records return the following formula value:

in progresscomplete

 Since the functions are not nested, there is not a way to guarantee that both conditions cannot be met and therefore both values returned in a malformed, unintended way.

A potential solution might be to swap the first formula I provided you with this:

IF(
    {% Complete} = 100,
    'complete',
    IF(
        {task assigned?},
        'in progress'
    )
)

Here, we ensure that if the % Complete field value returns 100, that the only possible thing that could be returned from the formula is your intended complete value, regardless of the condition returned by the task assigned? field.