# Re: Handling "Infinity" errors and IF statement formatting

Solved
1371 0
cancel
Showing results for
Did you mean:
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.

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

2 Solutions

Accepted Solutions
6 - Interface Innovator

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

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

3 Replies 3
6 - Interface Innovator

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

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