Value based on column numbers

[hsandeep]

Column A2:A9999 contains values. B1=500 & C1=1000.
If any value in the column A2:A9999 is either of the 2 range B1 & C1 i.e <B1 or >C1,
answer D1=1 else 0.
Column A2:A9999 may also contain #N/A or alphanumeric generated thr' formula which has to be ignored.
i.e. GDR8709 or HHH or #N/A has to be ignored.

 

[DamageZ]

Hi Hsandeep,

I hope I understand your query correctly;

If any value in Col A is equal to B1 or C1 then D1 = 1 else 0?

If so, type this into D1

=IF(OR(IFERROR(MATCH(B1,A2:A9999,0),0)>0,IFERROR(MATCH(C1,A2:A9999,0),0)>0),1,0)

Hope this helps,
Adam.

 

[hsandeep]

Little difference:
Instead of If any value in Col A is equal to B1 or C1 then D1 = 1 else 0
it is;
If any value in Col A is Less than B1 or greater than C1 then D1 = 1 else 0?

 

[DamageZ]

Try this one;

=IF(OR(COUNTIF(A2:A9999,"<"&B1)>0,COUNTIF(A2:A9999,">"&C1)>0),1,0)

 

[Aladin Akyurek]

Column A2:A9999 contains values. B1=500 & C1=1000.
If any value in the column A2:A9999 is either of the 2 range B1 & C1 i.e <b1 or="">C1,
answer D1=1 else 0.
Column A2:A9999 may also contain #N/A or alphanumeric generated thr' formula which has to be ignored.
i.e. GDR8709 or HHH or #N/A has to be ignored.

Given:

A2: 700
A3: 1000
A4: #N/A
A5: KAD
A6: 10
A7: 500

What is the outcome you want to see?

</b1>

 

[hsandeep]

Little bit more:
A1444=31000
B1=31001
C1='blank' since yet to be punched at this moment & the answer HAS ALREADY popped up as 1
I want to punch C1=30999 & then the answer should be 1 since A1444 has value which meets BOTH criteria <B1 & >C1

 

[Aladin Akyurek]

Given:

A2: 700
A3: 1000
A4: #N/A
A5: KAD
A6: 10
A7: 500

What is the outcome you want to see?

Little bit more:
A1444=31000
B1=31001
C1='blank' since yet to be punched at this moment & the answer HAS ALREADY popped up as 1
I want to punch C1=30999 & then the answer should be 1 since A1444 has value which meets BOTH criteria <b1 &="">C1

Please try to answer the question you are posed...

</b1>

 

[hsandeep]

Outcome=0 since no value in the range is less than 500 AND greater than 1000. Had A8:1005, then outcome=1

 

[Aladin Akyurek]

Outcome=0 since no value in the range is less than 500 AND greater than 1000. Had A8:1005, then outcome=1

Is it...

=OR(COUNTIF(A2:A9999,"<"&B1),COUNTIF(A2:A9999,">"&C1))+0

 

[hsandeep]

This works. But i found little more....
I want to find whether the range contains either of 600, 601, 602 or not; and I would be punching in B1=603 & C1=599.