Help with Nested If/And

[milehigh]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I need help... <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape style="WIDTH: 11.25pt; HEIGHT: 11.25pt; VISIBILITY: visible; mso-wrap-style: square" id=Picture_x0020_3 alt="Description: http://www.mrexcel.com/forum/images/smilies/icon_smile.gif" type="#_x0000_t75" o:spid="_x0000_i1026"><v:imagedata o:title="icon_smile" src="file:///I:\SYSTEM~2\msohtmlclip1\01\clip_image001.gif"></v:imagedata></v:shape>I am working on a calculator to track cancer tumor measurements - and based of certain calculations the sheet will indicate based off calculations the tumor response.<o></o>

A little background so it makes a little sense - I hope - There are certain criteria that they have to meet in order to be considered Complete Response, Partial Response, Stable, or Progressive. The problem I am having is that if there are 2 different types of lesions (lymph nodes or non-lymph nodes) the response is calculated differently - the non lymph nodes are easy if they go down, disappear, or increase it is straight forward but if you throw a lymphnode into it that is where I have a problem - as if they go below a certain point (10) they are considered normal - but I don't know how to have my formula check a set of cells to verify that the rest of the non tumor lesions are at zero<o></o>

I don't know how to insert a picture here or I would show you a screen shot.<o></o>

Here is part of my formula (There are other conditions that are being checked too - but those are working appropriately).<o></o>
The first AND Tells me that the user has indicated that the lymphnodes are considered to now be normal (all less than 10) - I then need to check 5 other cells to see if they are not a lymphnode (the $C$24 - $C$28 cells - contain yes or no) and if that value no then the corresponding measurement must be 0 (the E24 - E28 cells)<o></o>

IF(AND(E33="Yes",(Or(AND(($C$24="No",E24=0),(AND($C$25="No",E25=0),(AND($C$26="No",E26=0),(AND($C$27="No",E27=0), (AND($C$28="NO",E28=0),"CR","SD"<o></o>

Not sure if this can be done this way. Any other suggestions would be greatly appreciated. Thanks.

 

[Dryver14]

maybe this needs a little more explaining as it appears to me you could put the formula in E24

=IF(C24="No",0,"")

This returns a blank if not no what are the CR and SD at the end of your formula?

 

[jasonb75]

Maybe

=IF(AND(E33="Yes",COUNTIF(C24:C28,"No")=5,COUNTIF(E24:E28,0)=5),"CR","SD")

Another way would be

=IF(AND(E33="Yes",(C24:C28="No")*(E24:E28=0)),"CR","SD")

Confirmed as array formula with Shift Ctrl Enter, but I think the first would be more efficient.

 

[milehigh]

Hi,

Thanks for the suggestions -

@Dryver - I need to check that there is a yes in e33, then I need to check each cell c24 - c28 - if that cell is no - then I need to make sure that the corresponding cell in row e is 0.

@Jason - that doesn't work - that requires that all cells c24-c28 are no and that may not be the case.

 

[jasonb75]

Assuming no negative values, try

=IF(AND(E33="Yes",MAX(IF(C24:C28="no",E24:E28))=0),"CR","SD")

again, array confirmed with shift ctrl enter.

Or if you're using excel 07 or newer, non-array formula.

=IF(AND(E33="Yes",COUNTIFS(C24:C28,"no",E24:E28,"<>0")),"CR","SD")

 

[jasonb75]

Correction to last one, I was trying to add it to the last reply before the 10 minute editing deadline expired and didn't check it properly.

Should be

=IF(AND(E33="Yes",COUNTIFS(C24:C28,"no",E24:E28,"<>0")=0),"CR","SD")

 

[milehigh]

Hi Jason,

I am pretty sure the last one worked (I tested it out but am having a second set of eyes look too). I don't know much about array formulas but thank you again.