Removing NaN or Infinity from cell results

#1

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!

#2

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

2 Likes
#3

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!

1 Like