If with 3 criteria

[hsandeep]

Sheet1

	A	B	C	D	E
Names				Result
Michael
John
Simon
James

<colgroup><col style="FONT-WEIGHT: bold; WIDTH: 30px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]2400[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"]4500[/TD]
[TD="align: right"]4500.15[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1500.65[/TD]
[TD="align: right"]14900[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1823.55[/TD]
[TD="align: right"]1400.25[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]0[/TD]

</tbody>

if Names is John or James Column E=1 if D>=C else 0.
if Names is OTHER THAN John or James Column E=1 if D>=B else 0.
For error E=null.
What should the formula be in column E?
Thanx in adv.

 

[bbott]

In E2:

=IF(AND(OR(A2="John",A2="James"),D2>=C2),1,IF(D2>=B2,1,0))

 

[Special-K99]

Try (untested)

in E2
=IF(OR(AND(OR(A2="John",A2="James"),D2>=C2),AND(AND(A2<>"John",A2<>"James"),D2>=B2)),1,0)

 

[par60056]

I'm not sure what you want to test for error. I'm sure you can add that.

Would this work?

=IF(OR(A2="James",A2="John"),IF(D2>C2,1,0),IF(D2>B2,1,0))

 

[par60056]

In E2:

=IF(AND(OR(A2="John",A2="James"),D2>=C2),1,IF(D2>=B2,1,0))

I don't think that this works. If john or james and d2 < c2 and d2 > <c2 but="" d2="">b2 they will get a 1</c2>

 

[Special-K99]

@bbott

That will produce 1 if...

AND(A2 < > "John",A2 < > "James") AND D2 < C2 AND D2 > = B2

which may or may not happen

 

[hsandeep]

Additionally:
column F=C if Names is John or James else B
How to accomplish?

 

[par60056]

F= =IF(OR(A2="James",A2="John"),C2,B2)

 

[hsandeep]

Oops! was so simple...Thanks par60056