IF NaN Formula

evan.townson

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

ben_speight

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

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

ryan_kohn

@alexpavel has authored a nice post on this topic containing various options: https://community.anaplan.com/t5/How-To/How-To-Convert-Infinity-Infinity-NaN-to-Zero-Value/ta-p/117120