Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Handling "Infinity" errors and IF statement formatting

Solved
Jump to Solution
2123 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Dennis_Yu
6 - Interface Innovator
6 - Interface Innovator

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.

Screen Shot 2023-01-31 at 12.48.43 PM.png

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)

Screen Shot 2023-01-31 at 12.50.33 PM.png

I want the data to reflect as blank. The following  is the formula used, that would have worked fine in excel/sheets.

Screen Shot 2023-01-31 at 12.53.09 PM.png
However, AirTable seems to have no idea to read this as a string or a number anymore since I cannot edit the format:

Screen Shot 2023-01-31 at 12.54.50 PM.png
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

2 Solutions

Accepted Solutions
Dennis_Yu
6 - Interface Innovator
6 - Interface Innovator

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.

See Solution in Thread

Ben_Young1
11 - Venus
11 - Venus

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.

See Solution in Thread

3 Replies 3
Dennis_Yu
6 - Interface Innovator
6 - Interface Innovator

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.
Ben_Young1
11 - Venus
11 - Venus

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