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(F16>99.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

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(F16>99.5%,10,0)

If F16 = "", then F16>99.5 = TRUE = 10

Try this:

=IF(AND(ISNUMBER(F16),F16>99.5%),10,0)

Biff

"Newsgal" <[email protected]> wrote in message
news:[email protected]...
> 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(F16>99.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

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(F16>99.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