Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Removing NaN or Infinity from cell results

Topic Labels: Formulas
Solved
Jump to Solution
5910 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Carrie_Hannah
4 - Data Explorer
4 - Data Explorer

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
Justin_Barrett
18 - Pluto
18 - Pluto

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
Justin_Barrett
18 - Pluto
18 - Pluto

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

Carrie_Hannah
4 - Data Explorer
4 - Data Explorer

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
4 - Data Explorer
4 - Data Explorer

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.