is there any formula IFZERO which is similar like IFERROR

[harinsh]

Hi Team,

Is there any formula in excel like IFZERO which is similar like IFERROR....return the require value if formula is ZERO....I just googled it but not found...can anyone help by creating in UDF or share if any other formula to get the desire output.


Thank you,

 

[Rick Rothstein]

I think this UDF will work, simply pass it the expression to evaluate as the first argument and what you want returned if that evaluation is 0...

Function IFZERO(Expr As Variant, ValIfZero As Variant) As Variant
  Dim Calc As Variant
  Calc = Evaluate(Expr)
  If Calc = 0 Then
    IFZERO = Evaluate(ValIfZero)
  Else
    IFZERO = Evaluate(Expr)
  End If
End Function

 

[Aladin Akyurek]

Something like would be similar in behavior...

=IF(ISNUMBER(1/...),"X","Y")

 

[Tetra201]

You can use IFERROR with a minor twist:

=IFERROR(1/(1/value),value_if_zero)

Obviously, it will also return value_if_zero if value evaluates to an empty string, text string, or error.

 

[Aladin Akyurek]

Explanation of 1/(1/X)
https://www.mrexcel.com/forum/excel-questions/922308-return-earliest-date.html

Here is the reason why this usage should be abandoned:
https://www.mrexcel.com/forum/excel-questions/948721-index-match-excluding-duplicates.html

 

[dmikhailov4]

The problem with the non-Macro solutions (even these are often a last resort) proposed is that having to

You can use IFERROR with a minor twist:

=IFERROR(1/(1/value),value_if_zero)

Obviously, it will also return value_if_zero if value evaluates to an empty string, text string, or error.

Clever!

 

[chasfh]

Absent any command like IFZERO, just use a basic IF formula:

=IF(value=0,"",value)

 

[Scott Huish]

You could drop the =0 part if you just use the FALSE part for that.

=IF(A1,A1,"Zero")