4 Replies Latest reply: May 15, 2014 10:06 AM by BradH _ RSS

    Remove "0" in calculated field

    JLain _

      Looking for a formula on a If statement in a calculated field which will replace a "0" result with a blank. That way I can count lines with different categories for responses greater or lesser than "0"

        • Remove "0" in calculated field
          Data Kruncher

          You'll have to use a calculated field, which I'm certain that you know, but do  you still want the resultant field to be Numeric? It would have to be Character if "0" literally became " ". You could continue with a Numeric if you convert zeros to null values.

          If(MyField=0,1/0,MyField)[/code]I might use a different approach altogether if you just want to count lines. I'd build a numeric field named IncludeInCount with

           

          If(MyField=0,0,1)[/code]Then you can use IncludeInCount in a summary to determine how many non-zero responses you have.

           

          Does that help?

          • Remove "0" in calculated field
            BradH _

            Looking for a formula on a If statement in a calculated field which will replace a "0" result with a blank. That way I can count lines with different categories for responses greater or lesser than "0"[/QUOTE]

             

            I do something like this with formatting phone numbers--I use calculations to reformat the number, but want a blank instead of a zero when I don't have a valid phone number.

             

            The way I handle it is to make the field a character field, not numeric.  For the sake of keeping code more readable, I do this calculation in a field that I then hide.  For my exported field I do it like this:

             

            IF(>0,STR(CalculatedField),"")[/CODE]

             

            You could eliminate the use of the hidden field by nesting your calculation in place of the field reference.

             

            If you need to use the calculated field numerically elsewhere, you can reference the hidden numeric field (if you were using it in a Summary), or just reference the character field with a VAL() around it.