Function Assistance

Topic

New Reply

Is there a function with in Excel that returns zero for a undefined computation such as in 100 / 0?

Thanks,
John

Reply | Quote

Replies

In Excel 2003 and before, you can use IF in combination with ISERROR, e.g.

=IF(ISERROR(A1/A2),0,A1/A2)

In Excel 2007 and later, you can use IFERROR, e.g.

=IFERROR(A1/A2,0)

Reply | Quote

Is there a function with in Excel that returns zero for a undefined computation such as in 100 / 0?
John

Or try this,

can be used in all Excel versions and without ISERROR testing function :

=IF(A2,A1/A2,0)

Regards
Bosco

Reply | Quote

Or try this,

can be used in all Excel versions and without ISERROR testing function :

=IF(A2,A1/A2,0)

Regards
Bosco

While your solution works with numbers entered in A1:A2; if text is entered in either cell an error is returned. The solution offered by Hans “=IF(ISERROR(A1/A2),0,A1/A2)” does not succumb to this trap.

Reply | Quote

While your solution works with numbers entered in A1:A2; if text is entered in either cell an error is returned. The solution offered by Hans “=IF(ISERROR(A1/A2),0,A1/A2)” does not succumb to this trap.

But, the OP asked for : ” How to return zero when computation in 100 / 0 ? ( or A1/A2 )”

I think the cell A1:A2 ( 100 / 0 ) were numbers

The ISERROR() definite not required

Regards
Bosco

Reply | Quote