Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions |