Skip to main content
Solved

Removing NaN where + and - values can exist

A

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!

Best answer by Marvel

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! 😊
View original
Did this topic help you find an answer to your question?

2 replies

M
  • Marvel
  • Participating Frequently
  • 11 replies
  • Answer
  • February 25, 2025

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! 😊
A
1 person likes this
  • A
    ashleyalward
A
  • ashleyalward
  • Author
  • New Participant
  • 2 replies
  • February 26, 2025
Marvel wrote:

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! 😊

This worked beautifully! Thank you very much 🙂 

Reply


Most helpful members this week

Terms & Conditions