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

    Formatting a calculation field

    jd3

      Title

      Formatting a calculation field

      Post

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

        • 1. Re: Formatting a calculation field
          philmodjunk

          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
            Sorbsbuster

            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
              jd3

              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
                philmodjunk

                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.