If statement help

[mkerstei]

I have a worksheet, and if cell 1 contains the word "Minimum," I need it to compare cells 2 and 3. I need it to run the equation in cell 4
=if(cell3>=cell2,"yes","no")
But if cell1 contains the word Maximum, I need it to compare cells 2 and 3 and run the equation:
=if(cell3<=cell2,"Yes","No")
I think you can use the =find statement, but I cannot get it to work. Any suggestions?

[swatsp0p]

I see two options...

1) the cell (e.g. A1) must contain either MINIMUM or MAXIMUM (no blanks or other values) use this formula:

=IF(A1="MINIMUM",IF(C1>=B1,"YES","NO"),IF(C1<=B1,"YES","NO")) --or this formula:
=IF(OR(AND(A1="MINIMUM",C1>=B1),AND(A1="MAXIMUM",C1<=B1)),"yes","no")

2) the cell (e.g. A1) may be blank or contain something other than Min. or Max.

=IF(A1="MINIMUM",IF(C1>=B1,"YES","NO"),IF(A1="MAXIMUM",IF(C1<=B1,"YES","NO"),"N/A")) --or this formula:
=IF(OR(AND(A1="minimum",C1>=B1),AND(A1="maximum",C1<=B1)),"yes",IF(OR(A1="MINIMUM",A1="MAXIMUM"),"NO","N/A"))

Is this what you were looking for?

Bruce

[swatsp0p]

One other option I just realized, is that the words MINIMUM or MAXIMUM may be contained within other text (e.g. "Value is MINIMUM")....

=IF(NOT(ISERROR(FIND("MINIMUM",A1)>0)),IF(C1>=B1,"YES","NO"),IF(NOT(ISERROR(FIND("MAXIMUM",A1)>0)),IF(C1<=B1,"YES","NO"),"N/A"))

note that the FIND function IS case sensitive. MINIMUM, minimum and Minimum are all different and, in my example, only MINIMUM will return the desired answer. The others will return "N/A".

this can be overcome by using SEARCH instead of FIND, as such:

=IF(NOT(ISERROR(SEARCH("MINIMUM",A1)>0)),IF(C1>=B1,"YES","NO"),IF(NOT(ISERROR(SEARCH("MAXIMUM",A1)>0)),IF(C1<=B1,"YES","NO"),"N/A"))


HTH

Bruce

[JE McGimpsey]

One way:


=IF(A1="Minimum",IF(A3>=A2,"Yes","No"), IF(A1="Maximum",
IF(A3<=A2,"Yes","No"),""))

In article <[email protected]>,
mkerstei <[email protected]>
wrote:

> I have a worksheet, and if cell 1 contains the word "Minimum," I need it
> to compare cells 2 and 3. I need it to run the equation in cell 4
> =if(cell3>=cell2,"yes","no")
> But if cell1 contains the word Maximum, I need it to compare cells 2
> and 3 and run the equation:
> =if(cell3<=cell2,"Yes","No")
> I think you can use the =find statement, but I cannot get it to work.
> Any suggestions?

[Bernie Deitrick]

Enter this in Cell 4, replacing the Cell1, Cell2, and Cell3 with the addresses of the appropriate
cells:

=IF(NOT(ISERROR(FIND("minimum",LOWER(Cell1)))),IF(Cell3>=Cell2,"yes","no"),IF(NOT(ISERROR(FIND("maximum",LOWER(Cell1)))),IF(Cell3<=Cell2,"yes","no"),"Neither"))

HTH,
Bernie
MS Excel MVP


"mkerstei" <[email protected]> wrote in message
news:[email protected]...
>
> I have a worksheet, and if cell 1 contains the word "Minimum," I need it
> to compare cells 2 and 3. I need it to run the equation in cell 4
> =if(cell3>=cell2,"yes","no")
> But if cell1 contains the word Maximum, I need it to compare cells 2
> and 3 and run the equation:
> =if(cell3<=cell2,"Yes","No")
> I think you can use the =find statement, but I cannot get it to work.
> Any suggestions?
>
>
> --
> mkerstei
> ------------------------------------------------------------------------
> mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
> View this thread: http://www.excelforum.com/showthread...hreadid=392939
>

[mkerstei]

Thank you! This solved my problem.