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 blank
or other values) use this formula:

=IF(A1="MINIMUM",IF(C1>=B1,"YES","NO"),IF(C1<=B1,"YES","NO")) --o
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?

Bruc

 

[JE McGimpsey]

One way:


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

 

[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

 

[swatsp0p]

One other option I just realized, is that the words MINIMUM or MAXIMU
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 an
Minimum are all different and, in my example, only MINIMUM will retur
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

Bruc

 

[mkerstei]

Thank you! This solved my problem