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
Tagged:
11
Answers
-
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/117120
0
