Formula Help IF AND OR i think?

[slinky192001]

Hello

I have some fields in a spreadsheet:

I2 - Numbers
BT2 - Names
AX2 - Names
CV2 - Numbers (Seconds)

I have written a formula to say that if the number in I2 is less than 1 and the names in BT2 and AX2 are the same and the CV2 is less than 3601 then Yes otherwise No which looks like this:

=IF((AND(I2<1,BT2=AX2,CV2<3601)),"Yes","No")

I need to change this to say that the names in BT2 and AX2 can be the same or the name in BT2 can be one of the following 2 names:

Paul Fisher
Darren Smith
Paul Turner

So keeping all criteria in the original formula i need to add a bit that says or if the names in BT2 are one of the above then Yes.

Hope this is making sense.

Thanks for any help

 

[jasonb75]

Try

=IF((AND(I2<1,OR(BT2=AX2,ISNUMBER(MATCH(BT2,{"Paul Fisher","Darren Smith","Paul Turner"},0))),CV2<3601)),"Yes","No")

 

[MrKowz]

Try:

<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120"></th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">I2<1,OR(<font color="Green">BT2=AX2,BT2={"Paul Fisher","Darren Smith","Paul Turner"}</font>),CV2<3601</font>),"Yes","No"</font>) </td></tr></tbody></table></td></tr></table><br />

 

[slinky192001]

Thank You!!!! Perfect

 

[slinky192001]

Try:

<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120"></th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">I2<1,OR(<font color="Green">BT2=AX2,BT2={"Paul Fisher","Darren Smith","Paul Turner"}</font>),CV2<3601</font>),"Yes","No"</font>) </td></tr></tbody></table></td></tr></table><br />

This one doesnt work.... if the name is the same it says no when it should say yes.

 

[jasonb75]

Try:

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10></TH><TD style="TEXT-ALIGN: left">=IF(AND(I2<1,OR(BT2=AX2,BT2={"Paul Fisher","Darren Smith","Paul Turner"}),CV2<3601),"Yes","No") </TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Works fine for me.