Removing NaN or Infinity from cell results

[Carrie_Hannah]

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!

[Justin_Barrett · Accepted solution]

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

Carrie_Hannah:

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

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

[Carrie_Hannah]

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!

[Katherine_Trave]

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!

[Justin_Barrett]

Welcome to the community, @Katherine_Traver1! :grinning_face_with_big_eyes:

Katherine_Traver1:

That all being said, did I miss something completely in the previous formula?

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.