How do I include the formula to return 0 when divided by zero?
 

I'm need to include the IF 0,0 formula in the formula below.
In this formula, C29 = Sales Goal, D29 = Sales Achieved, and E29 returns the
results. The formula is set up to account for times when there is a zero
goal, but a sale $ amount which means the sales rep gets 100% in E29 or, if
there is both a goal and sales acount they get the percentage of sales to
goal. However, when there is neither a goal or sales, the E29 result can't
return a #DIV/0! or the total cell that E29 rolls up into won't function
correctly. Help!

=IF(AND(C29=0,D290),100%,D29/C29)
--
News Gal

How do I include the formula to return 0 when divided by zero?
 

Try:
=IF(ISERROR(IF(AND(C29=0,D290),100%,D29/C29)),"",IF(AND(C29=0,D290),100%,D29/C29))

Or, if you want to show a zero, change the "" to 0
Regards,

"Newsgal" wrote:

I'm need to include the IF 0,0 formula in the formula below.
In this formula, C29 = Sales Goal, D29 = Sales Achieved, and E29 returns the
results. The formula is set up to account for times when there is a zero
goal, but a sale $ amount which means the sales rep gets 100% in E29 or, if
there is both a goal and sales acount they get the percentage of sales to
goal. However, when there is neither a goal or sales, the E29 result can't
return a #DIV/0! or the total cell that E29 rolls up into won't function
correctly. Help!

=IF(AND(C29=0,D290),100%,D29/C29)
--
News Gal

How do I include the formula to return 0 when divided by zero?
 

ISERROR will trap all errors. If you specifically want to trap when C29
is zero in order to avoid the #DIV/0 error, then try this:

=IF(AND(C29=0,D290),100%,IF(C29=0,0,D29/C29))

This returns zero instead of #DIV/0 - change the middle of the second
IF to ,"", instead of ,0, if you want a blank.

Hope this helps.

Pete

Myriam wrote:

Try:
=IF(ISERROR(IF(AND(C29=0,D290),100%,D29/C29)),"",IF(AND(C29=0,D290),100%,D29/C29))

Or, if you want to show a zero, change the "" to 0
Regards,

"Newsgal" wrote:

I'm need to include the IF 0,0 formula in the formula below.
In this formula, C29 = Sales Goal, D29 = Sales Achieved, and E29 returns the
results. The formula is set up to account for times when there is a zero
goal, but a sale $ amount which means the sales rep gets 100% in E29 or, if
there is both a goal and sales acount they get the percentage of sales to
goal. However, when there is neither a goal or sales, the E29 result can't
return a #DIV/0! or the total cell that E29 rolls up into won't function
correctly. Help!

=IF(AND(C29=0,D290),100%,D29/C29)
--
News Gal

How do I include the formula to return 0 when divided by zero?
 

Worked perfectly! Thanks!
--
News Gal


"Pete_UK" wrote:

ISERROR will trap all errors. If you specifically want to trap when C29
is zero in order to avoid the #DIV/0 error, then try this:

=IF(AND(C29=0,D290),100%,IF(C29=0,0,D29/C29))

This returns zero instead of #DIV/0 - change the middle of the second
IF to ,"", instead of ,0, if you want a blank.

Hope this helps.

Pete

Myriam wrote:

Try:
=IF(ISERROR(IF(AND(C29=0,D290),100%,D29/C29)),"",IF(AND(C29=0,D290),100%,D29/C29))

Or, if you want to show a zero, change the "" to 0
Regards,

"Newsgal" wrote:

I'm need to include the IF 0,0 formula in the formula below.
In this formula, C29 = Sales Goal, D29 = Sales Achieved, and E29 returns the
results. The formula is set up to account for times when there is a zero
goal, but a sale $ amount which means the sales rep gets 100% in E29 or, if
there is both a goal and sales acount they get the percentage of sales to
goal. However, when there is neither a goal or sales, the E29 result can't
return a #DIV/0! or the total cell that E29 rolls up into won't function
correctly. Help!

=IF(AND(C29=0,D290),100%,D29/C29)
--
News Gal

How do I include the formula to return 0 when divided by zero?
 

Thanks for the feedback.

Pete

Newsgal wrote:

Worked perfectly! Thanks!
--
News Gal


"Pete_UK" wrote:

ISERROR will trap all errors. If you specifically want to trap when C29
is zero in order to avoid the #DIV/0 error, then try this:

=IF(AND(C29=0,D290),100%,IF(C29=0,0,D29/C29))

This returns zero instead of #DIV/0 - change the middle of the second
IF to ,"", instead of ,0, if you want a blank.

Hope this helps.

Pete

Myriam wrote:

Try:
=IF(ISERROR(IF(AND(C29=0,D290),100%,D29/C29)),"",IF(AND(C29=0,D290),100%,D29/C29))

Or, if you want to show a zero, change the "" to 0
Regards,

"Newsgal" wrote:

I'm need to include the IF 0,0 formula in the formula below.
In this formula, C29 = Sales Goal, D29 = Sales Achieved, and E29 returns the
results. The formula is set up to account for times when there is a zero
goal, but a sale $ amount which means the sales rep gets 100% in E29 or, if
there is both a goal and sales acount they get the percentage of sales to
goal. However, when there is neither a goal or sales, the E29 result can't
return a #DIV/0! or the total cell that E29 rolls up into won't function
correctly. Help!

=IF(AND(C29=0,D290),100%,D29/C29)
--
News Gal