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

# Remove &quot;0&quot; in calculated field

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 &quot;0&quot; in calculated field

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 &quot;0&quot; in calculated field

I'll give it a go. Tks for the suggestion. I take it this is a nested If then statement on the back of the existing If statement I'm using?

• ###### Remove &quot;0&quot; in calculated field

Hello JLain,

If you are looking to avoid nested IF statements, you can try defining a calculated field as type "lookup". This is suitable if you have a finite and static range of input values.

HTH,

Olly

• ###### Remove &quot;0&quot; in calculated field

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.