IF NaN Formula
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?
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.2
Thanks Ben. Sounds like we are on the same page in regards to the ISBLANK enhancement request.4
@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/1171200