Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel - IF Formula and #DIV0!
How can we get the formula below to drive the correct response when a zero is
the divisor? Points earned are SUMMED at the bottom of the spreadsheet which corrupts when the Goal is zero creating a #DIV/0! message is in any of the cells in the SUM range. However, if we correct so #DIV/0! doesn't display, then a zero Goal and zero Actual will drive a 10 points earned result instead of zero points earned. Goal Actual Variance % Points Possible Points Earned 0 0 =D16-C16 =D16/C16 10 =IF(F1699.5%,10,0) Goal Actual Variance % Points Possible Points Earned $0.00 $0.00 $0 #DIV/0! 10 #DIV/0! Corrected to not display #DIV/0!: Goal Actual Variance % Points Possible Points Earned $0.00 $0.00 $0 10 10 -- News Gal |
#2
|
|||
|
|||
Hi!
However, if we correct so #DIV/0! doesn't display How are you correcting? Are you using a formula that returns a formula blank("")? If that's the case then what's happening is that the formula blank is a zero length TEXT string and a TEXT value will ALWAYS evaluate to be greater than a numeric value. So: =IF(F1699.5%,10,0) If F16 = "", then F1699.5 = TRUE = 10 Try this: =IF(AND(ISNUMBER(F16),F1699.5%),10,0) Biff "Newsgal" wrote in message ... How can we get the formula below to drive the correct response when a zero is the divisor? Points earned are SUMMED at the bottom of the spreadsheet which corrupts when the Goal is zero creating a #DIV/0! message is in any of the cells in the SUM range. However, if we correct so #DIV/0! doesn't display, then a zero Goal and zero Actual will drive a 10 points earned result instead of zero points earned. Goal Actual Variance % Points Possible Points Earned 0 0 =D16-C16 =D16/C16 10 =IF(F1699.5%,10,0) Goal Actual Variance % Points Possible Points Earned $0.00 $0.00 $0 #DIV/0! 10 #DIV/0! Corrected to not display #DIV/0!: Goal Actual Variance % Points Possible Points Earned $0.00 $0.00 $0 10 10 -- News Gal |
#3
|
|||
|
|||
In addition to Biff's response, you could change the % formula in F to be
=IF(c16=0,0,D16/C16) and use your current Points Earned formula "Newsgal" wrote: How can we get the formula below to drive the correct response when a zero is the divisor? Points earned are SUMMED at the bottom of the spreadsheet which corrupts when the Goal is zero creating a #DIV/0! message is in any of the cells in the SUM range. However, if we correct so #DIV/0! doesn't display, then a zero Goal and zero Actual will drive a 10 points earned result instead of zero points earned. Goal Actual Variance % Points Possible Points Earned 0 0 =D16-C16 =D16/C16 10 =IF(F1699.5%,10,0) Goal Actual Variance % Points Possible Points Earned $0.00 $0.00 $0 #DIV/0! 10 #DIV/0! Corrected to not display #DIV/0!: Goal Actual Variance % Points Possible Points Earned $0.00 $0.00 $0 10 10 -- News Gal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|