Skip to main content

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 %.

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.


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.


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


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} & “”


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


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


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 ia 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!


Reply