Formatting a Formula with an IF statement NEW to Airtable

[Bob_Tambornini]

Hello all.

I am very excited to be working in Airtable. I have ran across an issue I cannot solve. You help would be greatly appreciated.

I feel it is a simple formula.
1 Table
2 Fields

Qty of Records Qty Records Found %Found
300 250 I have this formula written no issues.
*100-({SAP Records from Sitebook}-{SAP Records Captured})/{SAP Records from Sitebook}100

The issue is when I do not have data in either field I get a NaN. I don’t want the NaN error to show. So I wrote an IF blank formula, The formula works, but now I cannot format the formula to %.

[W_Vann_Hall]

Easy workaround: Format it as a string and append the percent sign:

IF(
    AND(
        {SAP Records from Sitebook},
        {SAP Records Captured}
        ),
    YOUR_FORMULA_GOES_HERE & '%',
    BLANK()
    )

Admittedly less than optimal — but given Airtable’s flaky handling of Percentage fields, it’s not that much of a pain, as you’ll have to tweak either value (divide percentage by 100 or wrap the string in VALUE() and divide the result by 100) should you need to use it in a formula.

[Bob_Tambornini]

Thank you so much Everyone in here is so nice and helpful. Thank you again so much.

[Bob_Tambornini]

Van, I saw this response you offered up before. Is this what your trying to tell me to do

W_Vann_Hall
Jul '17

I realize you’ve already figured this out, but as a breadcrumb for future searchers, to convert a numeric value to a string, simply concatenate it with the empty string (""):

CONCATENATE({NumericValue},"")

or

{NumericValue} & “”

[Bob_Tambornini]

He is my formula now. I am not following how to put it all together with the ‘%’ sign.

IF({Inv. Start Date}="","",100-({SAP Records from Sitebook}-{SAP Records Captured})/{SAP Records from Sitebook}*100)

If you could help a little more I would greatly appreciate it.

Thanks

[W_Vann_Hall]

Bob -

I meant it quite literally: Replace YOUR_FORMULA_GOES_HERE with your formula. :winking_face:

IF(
    AND(
        {SAP Records from Sitebook},
        {SAP Records Captured}
        ),
    IF(
        {Inv. Start Date}="",
        "",
        (100-({SAP Records from Sitebook}-{SAP Records Captured})/{SAP Records from Sitebook}*100)&'%'
        ),
    BLANK()
    )

That first IF() statement checking to make sure {SAP Records from Sitebook} and {SAP Records Captured} both exist simply prevents you from receiving a ‘not a number’ (NaN) error message; if that duplicates what your initial IF() statement does by checking {Inv. State Date}, you can eliminate that clause of mine:

IF(
    {Inv. Start Date},
    (100-({SAP Records from Sitebook}-{SAP Records Captured})/{SAP Records from Sitebook}*100)&'%',
    BLANK()
    )

(I prefer using BLANK() rather than '' simply because in some [a few] instances, Airtable treats them differently.)

The act of concatenating the numeric result of your formula with the string '%' automatically converts the final result into a string; you don’t need to perform an explicit conversion with ‘&''’ first.

Hope this helps!