nested if help with DIV/O error

[milehigh]

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
showing up or displaying 0%. Please help.

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.

 

[sh0t2bts]

Try This

=IF(ISERROR(Your Formula),0,Your Formula)


Mark

 

[Harlan Grove]

...
...

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
showing up or displaying 0%. Please help.

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

...

There are questions *you* must answer first.

Should any of the preceding cells in row 28 evaluate to zero? If so, why? If
not, shouldn't your formula produce an error of some kind?

I suppose you could convert your formula to an array formula and use

MIN(IF($C$2828>0,$C$2828))

rather than just MIN($C$2828), but you may get meaningless results.

This is a situation in which there may be several ways to address the symptoms
of what's ailing your formula, but truly fixing the formula would seem to
require fundamental change in the formula or in your worksheet's layout. What do
these values represent? You mention you're tracking nadir values. If those nadir
values could be nonpositive, they don't provide a sound basis for calculating
meaningful percentages *OR* once they turn nonpositive (so <=0) the only useful
information is qualitative or absolute, not relative.

 

[Guest]

Hi Harlan,
The spreadsheet that I have this formula is tracking
cancer tumor cells - there are 2 things we are tracking
from the baseline scans - the change in each scan from
baseline and the change in size from the lowest value
(the nadir). What happens is if the tumor decreases by
30% then that is considered a partial response, if it
increases by 20% of baseline or the nadir - it is
considered progressive disease if it goes to 0% it is
considered complete response but if after there is a
complete response if there is any tumor regrowth there it
is considered progressive disease.

I hope this helps clarify why I am dealing with negative
numbers (which is what we want in this field !

I would be happy to email you the file so you could look
at it if it would make it clearer. Thanks for your help -
if you need additional information please let me know.

 

[Harlan Grove]

The spreadsheet that I have this formula is tracking
cancer tumor cells - there are 2 things we are tracking
from the baseline scans - the change in each scan from
baseline and the change in size from the lowest value
(the nadir). What happens is if the tumor decreases by
30% then that is considered a partial response, if it
increases by 20% of baseline or the nadir - it is
considered progressive disease if it goes to 0% it is
considered complete response but if after there is a
complete response if there is any tumor regrowth there it
is considered progressive disease.

I hope this helps clarify why I am dealing with negative
numbers (which is what we want in this field !

I would be happy to email you the file so you could look
at it if it would make it clearer. Thanks for your help -
if you need additional information please let me know.

...

You don't need to e-mail the file. I wouldn't look at it. Nothing personal.

All I can say is that you have a problem. The last biology course I took was in
secondary school when I was 16 - a long time ago. However, I have degrees in
mathematics, and I do know it's *IMPOSSIBLE* to divide by zero. If your nadir is
zero, you can't use your nadir as the basis for meaningful percentages, and I'd
find it very highly surprising if anyone in your field (oncology?) uses the
exact statistic you want to calculate.

Obviously there's no baseline before the initial observation/sampling/specimen.
Presumably you don't attempt to calculate percentage change from prior nadir at
the initial observation. I imagine you should be treating subsequent zero
observations as 'resets', so the observation immediately following a zero
observation should be treated as if it were an initial observation. Would you
really consider a zero nadir in a situation such as the nadir through the N-1_th
observation being positive, the N_th observation zero so the nadir becomes zero,
but the N+1_th observation is positive again to be a true zero in the N_th
observation or a false measurement?

While I've never done biostatistics, I'm certain biostats doesn't differ from
other stats so much that there are generally accepted conventions for dividing
by zero. If you're trying to develop a new form of analysis, looks like it's
time for a fundamental redesign.