Formula to remove NaN when 0/0

[ashleyalward]

Hi there! I am trying to remove NaN values from a formula field where both positive and negative values exist. This is in an effort to clean up two extensions: One chart indicating the number of each value's occurrence and one number where I am trying to get the average of the values, but with NaNs included I receive an infinity. 

Here is my current formula:

DATETIME_DIFF(

{Due Date},

{Date Completed},

'days'

)

+

IF({Date Completed},{Due Date} = "NaN", BLANK())

Thank you in advance!

[Marvel · Accepted solution]

It looks like your formula has an issue causing NaN values. To fix this, you can update your formula to make sure it only calculates when both dates exist. Try this:

IF(AND({Due Date}, {Date Completed}), DATETIME_DIFF({Due Date}, {Date Completed}, 'days') )
This will prevent NaN values and keep your charts and averages clean. If you need more help, feel free to ask! 😊

[ashleyalward]

This worked beautifully! Thank you very much 🙂