Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

How to get rollup to ignore NaN

Topic Labels: Formulas
Solved
Jump to Solution
4034 8
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

1 Solution

Accepted Solutions

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() )

See Solution in Thread

8 Replies 8

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.

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.

It works for me. Here is a quick example Base I just knocked up.

Yes, it’s now working! I had to logout a few minutes ago though, so maybe just a refresh was in order.

thanks. it works for me!

Thank you! This worked for me.