2 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Numeric Field n/a

    Oli _

      Hi,

      I'd like to display as a result of calcualtion in a nummeric field "n/a", like Excel does.

       

      for example:

      IF(Net Sales<0;"n/a";Net Sales*0,3)

       

      With this result I'd like to make further calculations.

       

      I sthis possble for monarch ?

       

      greetings Oli

        • Numeric Field n/a
          Nigel Winton

          Oli

          When you put something such as "n/a" in a formula Monarch treats it as a character field. However all is not lost.

           

          If(=0,"n/a",str((),10,2,""))

           

          This will give you a character field with the n/a showing. Then you can use the Val function to change that field back to numeric in any other calculated field. The n/a will change to a zero when you use the Val function.

          So you should end up with a field showing the n/a where you need it and other fields being calculated from it.

           

          Regards

           

          Nigel

          • Numeric Field n/a
            Grant Perkins

            Oli,

             

            I am wondering it you want to make the result of all fields less than 0 a null field (in effect) or for them to hold the REAL value but display as "n/a".

             

            For example you could could use a formula like

             

            IF(YOURFIELD<0, YOURFIELD/0, YOURFIELD*.03)

             

            This would give a NULL result if the value was less than 0 and then you could set your options to display null as n/a.

             

            However further calculations with the NULL value would not be possible unless a zero result was acceptable - you would need to perform the calculation from the ORIGINAL field - i.e. YOURFIELD.

             

            Does this help?

             

            Grant