Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Removing NaN or Infinity from cell results

Topic Labels: Formulas
Solved
Jump to Solution
3154 4
cancel
Showing results for 
Search instead for 
Did you mean: 

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!

1 Solution

Accepted Solutions

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

58%20PM

See Solution in Thread

4 Replies 4

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

58%20PM

Thank you so much! That was a typo, my apologies, this is exactly perfect! That is so simple and exactly what I needed. I am really frustrated with myself that I couldn’t seem to make it work on my own. I’ve been struggling to get this to work all afternoon, you are officially my hero!

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!

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.