This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Removing NaN or Infinity from cell results

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
1916
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 17, 2019 07:32 AM

I am attempting to calculate a cost per validation in a table using a Formula of two rollup Fields but I want the results to show either the resolution of the problem {column A}/{column B} or “0” if NaN or the sum of Field 1 if Infinity.

Examples of problems:

if

Field 1 rollup is $1800.00

Field 2 rollup is 0

I need the formula in Field 3 {column A}/{column B} to show $1800.00 not Infinity

if

Field 1 rollup is $0.00

Field 2 rollup is 0

I need the formula in Field 3 {column A}/{column B} to show $1800.00 not NaN

if

Field 1 rollup is $1800.00

Field 2 rollup is 2

I need the formula in Field 3 {column A}/{column B} to show $900

Getting the formula to give me the last result is easy enough - but I can’t seem to figure out how to use nested if formula to give me either 0 or Feild 1 amount.

Is anyone else able to help me build this formula? Please and thank you so much if so!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 17, 2019 11:25 AM

I’m guessing there’s a typo in this:

Based on your topmost comment, I think you want $0, not $1800.

With that in mind, here’s my formula for `{Field 3}`

:

```
IF({Field 2}, {Field 1} / {Field 2}, IF({Field 1}, {Field 1}, 0))
```

Reply

4 Replies 4

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 17, 2019 11:25 AM

I’m guessing there’s a typo in this:

Based on your topmost comment, I think you want $0, not $1800.

With that in mind, here’s my formula for `{Field 3}`

:

```
IF({Field 2}, {Field 1} / {Field 2}, IF({Field 1}, {Field 1}, 0))
```

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 17, 2019 01:39 PM

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 09, 2020 06:21 AM

For anyone new coming to this thread, this formula didn’t work for me (got an “Invalid Formula” error message), but I have successfully used:

`IF({Field 1} = 0, BLANK(), IF({Field 2} = 0, {Field 1}, {Field 1} / {Field 2}))`

Replace `BLANK()`

with `0`

if you want to pull through 0 - that’s down to need and personal preference.

That all being said, did I miss something completely in the previous formula? Was I supposed to input my own logical statements in there somewhere? :woman_shrugging: Oh well, got there in the end!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 09, 2020 08:19 AM

Welcome to the community, @Katherine_Traver1! :grinning_face_with_big_eyes:

Hard to say without having seen what you did when trying to use the formula. Sometimes copying and pasting goes awry. Maybe you missed a parenthesis or copied an extra character by mistake.