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!
