Re: Fill in cell as '1' if the formula gives 'NaN'.

356 0
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast


The cell gives an error (NaN) when the linked table does not contain any linked records. I want to create a formula that will fill-in the cell as '1' if the 'average (values)' is not possible. 

Thank you.

3 Replies 3
6 - Interface Innovator
6 - Interface Innovator

Okay so one way to do this is to add some sneaky formula logic to the rollup formula:

IF( values, AVERAGE(values))

 So IF the rollup has values (or if there are linked records), do the AVERAGE, but IF NOT, it outputs nothing.


One thing to be aware of are 'ghost' or null values that may throw this off. So alternatively you could toggle on "Only include records from the..." option and filter to just records with a value in the Exchange rate field

Thank you very much. This is already very useful.

Now I want the empty cells to be filled with the number 1, because it is used for a calcalution of another cell. Could that be added to the formula? 

Thank you!

Oh, yes, you could add that to the end of the IF() function:

IF( values, AVERAGE(values),1)