Nested IF question

  • Thread starter Thread starter milehigh
  • Start date Start date
M

milehigh

Hi,

I have a value in Cell 1 that I need to check in numerous
ways (including against another cell) and then have some
text displayed.

If the value in Cell 1 is >=.2 or Cell2 = "YES" then Cell
3 needs to display "PD"

If the value in Cell 1 is <=-0.3 then cell 3 needs to
display "PR"

If the value in Cell 1 is <=-0.1 then cell 3 needs to
display "CR"

If the Value in Cell 1 doesn't meet any of the criteria
above then display "SD"

This is what I have started with but something is not
right:
=IF((OR(D31="YES",D29>=0.2)),"PD",(OR((D29<=-0.3),"PR",(OR
((D29<-1),"CR","SD")))))

But I am getting a value error in Cell 3. Any Help would
be greatly appreciated. Thanks in Advance.
 
Hi
try the following (A1 is your cell 1 and A2 your cell 2) in cell 3
=IF(OR(A1>=0.2,A2="YES"),"PD",IF(A1<=-0.3,"PR",IF(A1<=-0.1,"CR","SD)))

HTH
Frank
 
Hi Frank,

I had this formula before and it seems like it works
except for the <=-0.1 "CR"

I actually think I have screwed up my the below formulas -
maybe someone can help - I am dealing with a negative
numbers:

if the value in cell 1 = -1.0 and lower (i.e. -2.0)
then "CR" should display in cell 3

If the value in cell 1 = .99 through .30 then "PR" should
display in cell 3

If the value in cell 1 = .29 through .21 then "SD" should
display in cell 3

If the value in cell 1 = .20 and higher then "PD" should
display in cell 3.

Thanks again - this is driving me crazy......
 
Hi
thats not fair, you change the specification :-)
First i think you forgot the negative sign for your condition no. 2 - 4
(e.g. -0.99 to -0.30)
Try the following
=IF(A1<=-1.0,"CR",IF(A1<=-0.30,"PR",IF(A1<=-0.21,"SD",IF(A1>=-0.20,"PD"
,"no message/no value"))))

some note:
- in your previous specification you also include a cell 2?
- the above will return 'no message/no value' for values between -0.21
and -0.20 (e.g. -0.205) as you haven't specified this condition

HTH
Frank
 
Hi Frank,

Sorry - My head has been spinning around this for a few
hours - okay let me clarify

if the value in cell 1 = -1.0 and lower (i.e. -2.0)
then "CR" should display in cell 3

If the value in cell 1 = -.99 through -.30 then "PR"
should display in cell 3

If the value in cell 1 = -.29 through .19 then "SD"
should display in cell 3

If the value in cell 1 = .20 and higher or if cell 2
= "yes" then "PD" should display in cell 3.

As an FYI - I am creating a spreadsheet to track cancer
tumor measurements - the value in cell one is a
percentage of change from the baseline measurements.

Thanks again - this is driving me crazy......

I think this is what I need - (which seems to work) but
is there a better way?

=IF(A1<=-1.0,"CR",IF(A1<=-0.30,"PR",IF(A1<=.19,"SD",IF(OR
(A1>=0.20,A2="Yes"),"PD"))))
 
Hi
this should do it (hopefully):
=IF(A1<=-1.0,"CR",IF(A1<=-0.30,"PR",IF(A1<=-0.19,"SD",IF(OR(A1>=-.20,A2
="yes"),"PD" ,"no message/no value"))))
Frank
 
Hi Frank - methinks you have -ve signs you don't want in the 0.19 and the 0.20
at the end there. Also even when you lose the -ve signs, this doesn't trap
between 0.19 and 0.20, so the less than 0.19 might want to be less than 0.20
(But OP would need to clarify, as he has not yet done so).
 
Hi Ken
you're right. Its definetly too late -> time to stop working, soon
Regards
Frank
 
Just coming to that conclusion myself :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Frank Kabel said:
Hi Ken
you're right. Its definetly too late -> time to stop working, soon
Regards
Frank
Click to expand...
 
Back
Top