Skip to main content

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.

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

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!

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)

 

Reply


Terms & Conditions