4 Replies Latest reply on Jan 26, 2011 3:21 PM by philmodjunk

    Formatting a calculation field



      Formatting a calculation field


      Is there a way to format a calculated field to return "na" if the calculation currently shows a "?"    ?

        • 1. Re: Formatting a calculation field

          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.

          • 2. Re: Formatting a calculation field

            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'.

            • 3. Re: Formatting a calculation field

              To all,

              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...

              Many thanks!

              • 4. Re: Formatting a calculation field

                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.