Formatting a calculation field
Is there a way to format a calculated field to return "na" if the calculation currently shows a "?" ?
But why does the calculation show a "?" ?
This symbol will appear in a field that is too narrow to display the value. It will also appear when the value is the result of an invalid calculation such as dividing by zero, square root of a negative number. Invalid dates will also display with a ?.
By identifying the cause of the ?, you can then modify your calculation to return NA when this situation occurs by checking for zero, an empty field, value less than zero or...
There's also an IsValid function you may be able to use also.
Is the result of your calculation text or a number? You may have fun if it's a number, but you want the result to be the text 'NA'.
Thanks for helping.. The field is a number field and it is returning a "?" because the calculation is a division by zero. For my needs, I need to show the calculation but would like to replace the "?" with "na" as that looks better in the report. I feel sure that an "IF" statement will take care of this, but not sure how to do it in FMP. If you guys know of a way to make it work, I sure would appreciate it...
Let's say this is your current expression:
Field1 / field 2
You can rewrite this to be:
If ( Field 2 = 0 ; "NA" ; Field1 / field 2 )
As Sorbsbuster has commented, this could be a case of putting text into a field of type number. This works for many purposes, but not all, so test carefully.
Retrieving data ...