IF NaN Formula

evan.townson
Certified Master Anaplanner

IF NaN Formula

Hello,

 

I am running into issues with the "NaN" when running financial or misc. functions. This causes issues when attempting to aggregate to a parent level. My current solution is:

 

IF ISBLANK(TEXT(***function***) THEN 0 ELSE ***function***

 

Would be great if there was an "IFNaN" function to allow for cleaner/ quicker formulas. Anyone have a cleaner solution or agree this function would be valuable?

 

Thanks!

Evan

3 REPLIES 3
ben_speight
Super Contributor

ISBLANK(TEXT(x)) is the only reliable way I know of to detect NaNs. If x is computationally expensive then consider separating the calculation from the check. My suggestion for an enhancement request would be to simply extend ISBLANK to accept a numeric argument and result in TRUE if NaN, cutting out the unnecessary text conversion.
evan.townson
Certified Master Anaplanner

Thanks Ben. Sounds like we are on the same page in regards to the ISBLANK enhancement request.

ryan_kohn
Certified Master Anaplanner