# Thread: help with DIV/O error

1. Registered User
Join Date
Jan 2004
Posts
7

## Unanswered: help with DIV/O error

Hi,

I am having a hard time figuring out how to get my
formula to work without getting this error (DIV/0)

what I am trying to do is check a number in several cells
and then either display "PD" or the %of change - the
problem I have is once one of the cells goes to zero I
get the div/o error - what I want it do display is just
0% change.

so in my example formula below (in cell E30) needs to
check:

If Cell D30 >=2.0 or Cell D28=0 AND E28>0 then
display "PD"

If Cell D30="PD" and Cell E28>0 then display "PD"

that part seems to work fine - (and there may be an
easier way to do it - I am not sure) - the problem I run
into is when I run the next section of my formula:

I need it to check the value in Cell E28 subtract that
from the minumum value in the previous cells starting
with C28 (and in this example through D28) and then
divide it by the minmum number again (I am tracking a
nadir value). But what happens is that once the previous
cell (in this example d28) is 0 then I get the error. I
can't figure out a way to either prevent the error from

I have tried the below formulas without any success:

=IF(OR(AND(D30>=0.2),AND(D28=0,E28>0)),"PD",IF(AND
(D30="PD",E28>0),"PD",IF(E28=D28,"",(E28-MIN
(\$C\$2828))/MIN(\$C\$2828)))) - this gives a blank in
cell E30 but a "PD" in cell f30

=IF(OR(AND(D30>=0.2),AND(D28=0,E28>0)),"PD",IF(AND
(D30="PD",E28>0),"PD",IF(E28=D28,e30=0,(E28-MIN
(\$C\$2828))/MIN(\$C\$2828)))) - this gives a circular
reference but enters a 0 in cell e30 but then a zero in
all the following cells

Any suggestions? Thanks in advance.

2. Registered User
Join Date
Dec 2003
Location
San Diego, CA
Posts
153
I can't figure out a way to either prevent the error from
With this part, here's one that many may/will frown upon, BUT if you've got your formulas debugged, you can use =IF(ISERROR(Formula),"",Formula)) or =IF(ISERROR(Formula),"0%",Formula))

Note that ISERROR will prevent ALL error messages, not just DIV/0 and force your formulas to calculate 2x (in case processing speed is an issue).

You might want to look at a SUMPRODUCT alternative. Shades is good at those; I suck at 'em.

Hope that helps,

Smitty

3. Registered User
Join Date
Jan 2004
Posts
7
Hi Smitty,

I entered the following and get an error in the formula - I am not sure what is wrong:

=if(iserror(IF(OR(AND(D30>=0.2),AND(D28=0,E28>0)), "PD",IF(AND(D30="PD",E28>0),"PD",(E28-MIN(\$C\$2828))/MIN(\$C\$2828))),0,IF(OR(AND(D30>=0.2),AND(D28=0,E28>0)),"PD" ,IF(AND(D30="PD",E28>0),"PD",(E28-MIN(\$C\$2828))/MIN(\$C\$2828)))

the error is indicated at the 0 value between the two formulas.

Thanks.

4. Registered User
Join Date
Oct 2003
Posts
1,091
Try this formula for your main formula:

=IF(OR(D30>=0.2,AND(D28=0,E28>0)),"PD",IF(AND(D30= "PD",E28>0),"PD",(E28-MIN(\$C\$28: D28))/MIN(\$C\$28: D28)))

(now for display purposes because of the smilies, I added a space between : and D )

5. Registered User
Join Date
Oct 2003
Posts
1,091
Okay, here is you full formula. You were lacking a close parenthesis in the first part of the IF statement, which is why the 0 would cause an error.

=IF(ISERROR(IF(OR(D30>=0.2,AND(D28=0,E28>0)),"PD", IF(AND(D30="PD",E28>0),"PD",(E28-MIN(\$C\$28: D28))/MIN(\$C\$28: D28)))),0,IF(OR(D30>=0.2,AND(D28=0,E28>0)),"PD",IF (AND(D30="PD",E28>0),"PD",(E28-MIN(\$C\$28: D28))/MIN(\$C\$28: D28))))

Again, for display purposes, there is added a space between : and D, to prevent the smilies. So if, you copy this, be sure to take out those extra spaces.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•