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.
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)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.