Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jan 30, 2023 09:00 PM
Hi,
I have an issue when using the formula fields in AirTable when using an IF statement.
Context: I need the fields to be format as a percentage, or blank if there's an error/infinity.
Here is the formula that I want to use in its simplified form, and I can easily format it to return as a percentage. (FYI both fields used in this formula are rollup fields)
Sometimes, the values are 0, or blank, causing the data to reflect as such. (or infinity in some cases)
I want the data to reflect as blank. The following is the formula used, that would have worked fine in excel/sheets.
However, AirTable seems to have no idea to read this as a string or a number anymore since I cannot edit the format:
Is this intended, if so, is there a way for me to workaround this?
I am aware that it is possible to replace the value1 into 0 (instead of "" or ''), but that would cause a problem with another formula down the line.
My result end up becoming a long string of decimal that is hard to visualize eg.:
0.010466501695536614
Solved! Go to Solution.
Jan 30, 2023 09:12 PM
Replacing the "" part of the formula with BLANK() worked.
So instead of:
use:
Jan 30, 2023 09:45 PM
Hey @Dennis_Yu!
Glad to see you got this one resolved!
My recommendation would be to implement the following formula:
IF(
{All Clicks},
{All Clicks} / {All Impressions}
)
If I'm writing an IF() function that returns a blank or null on a true statement, then I find that it's much more concise to flip the logic of the evaluation parameter to your IF() function so that you only define what happens if something returns a truthy value.
It's a small detail that has no real consequence when used in an isolated formula, but can make a huge difference when dealing with an incredibly large and deeply nested formula.
It also goes a long way when thinking about readability.
Jan 30, 2023 09:12 PM
Replacing the "" part of the formula with BLANK() worked.
So instead of:
use:
Jan 30, 2023 09:45 PM
Hey @Dennis_Yu!
Glad to see you got this one resolved!
My recommendation would be to implement the following formula:
IF(
{All Clicks},
{All Clicks} / {All Impressions}
)
If I'm writing an IF() function that returns a blank or null on a true statement, then I find that it's much more concise to flip the logic of the evaluation parameter to your IF() function so that you only define what happens if something returns a truthy value.
It's a small detail that has no real consequence when used in an isolated formula, but can make a huge difference when dealing with an incredibly large and deeply nested formula.
It also goes a long way when thinking about readability.
Jan 30, 2023 09:50 PM
I see, that does make sense, thank you