Skip to main content

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

Replacing the "" part of the formula with BLANK() worked.

So instead of:

IF({All Clicks}=0, "" ,{All Clicks}/{All Impressions})

use:

IF({All Clicks}=0, BLANK() ,{All Clicks}/{All Impressions})

The NaN will become 0 and the formatting sticks.

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.

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.


I see, that does make sense, thank you

Reply


Terms & Conditions