I want to use a rollup to get an average of the existing numbers, but not all the fields in the column I want an average from have an existing number and return NaN. I want the rollup to ignore NaN.
Solved
How to get rollup to ignore NaN
Best answer by David_Skinner
Unfortunately that didn’t work - just got a fully blank column.
Maybe more context will help! I have one tab that has a function column =x/y, and in another tab I need the average of these as part of a rollup. I have tried using blanks where it returns “NaN”, but the rollup continues to return NaN regardless, I think because the “” is forced, but I have no idea.
Have you tried putting this in the function column (in the table you are rolling up from)?
IF( ISERROR( x/y )=0,x/y, BLANK() )
+14You can use the ISERROR() function combined with an IF statement. Something like:
IF( ISERROR(AVERAGE(values))=0, AVERAGE(values), BLANK() )
(Check this for formatting errors when you try it)
You can use the ISERROR() function combined with an IF statement. Something like:
IF( ISERROR(AVERAGE(values))=0, AVERAGE(values), BLANK() )
(Check this for formatting errors when you try it)
Unfortunately that didn’t work - just got a fully blank column.
Maybe more context will help! I have one tab that has a function column =x/y, and in another tab I need the average of these as part of a rollup. I have tried using blanks where it returns “NaN”, but the rollup continues to return NaN regardless, I think because the “” is forced, but I have no idea.
+14Unfortunately that didn’t work - just got a fully blank column.
Maybe more context will help! I have one tab that has a function column =x/y, and in another tab I need the average of these as part of a rollup. I have tried using blanks where it returns “NaN”, but the rollup continues to return NaN regardless, I think because the “” is forced, but I have no idea.
Have you tried putting this in the function column (in the table you are rolling up from)?
IF( ISERROR( x/y )=0,x/y, BLANK() )
Have you tried putting this in the function column (in the table you are rolling up from)?
IF( ISERROR( x/y )=0,x/y, BLANK() )
Yep, full column comes up blank and the formula automatically turns into IF( ISERROR( x/y )=0)) only - just gets rid of the rest of the formula after saving.
Yes, it’s now working! I had to logout a few minutes ago though, so maybe just a refresh was in order.
You can use the ISERROR() function combined with an IF statement. Something like:
IF( ISERROR(AVERAGE(values))=0, AVERAGE(values), BLANK() )
(Check this for formatting errors when you try it)
thanks. it works for me!
You can use the ISERROR() function combined with an IF statement. Something like:
IF( ISERROR(AVERAGE(values))=0, AVERAGE(values), BLANK() )
(Check this for formatting errors when you try it)
Thank you! This worked for me.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.