Highlighted
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

2 REPLIES 2
Expert

Re: IF NaN Formula

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.
Certified Master Anaplanner

Re: IF NaN Formula

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