If with 3 criteria

[hsandeep]

Sheet1

	A	B	C	D	E
1	Names				Result
2	Michael	2000	2500	2400	1
3	John	5000	4500	4500.15	1
4	Simon	1500.65	14900	1500	0
5	James	1823.55	1400.25	1400	0

<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>
</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]

ADVERTISEMENT

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]

ADVERTISEMENT

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