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.

 

[Aladin Akyurek]

This works.

You are welcome.

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.

That's vague. Try to be specific by way of an example. That is, as have done, construe a small sample and word the outcome you want to see using that sample.

 

[hsandeep]

A1:20
A2:200
A3:450
A4:#N/A
A5:600
A6:9999
A7:9998
A8:10000
I want to check whether the range consists 9999 or not. If yes then 1 else 0.
I thought of an idea to use B1=10000 & C1=9998 so that 9999 is 'trapped' in between as <10000 (<B1) & >9998 (>C1).
Another idea would also be welcomed. Result/goal is same.

 

[Aladin Akyurek]

A1:20
A2:200
A3:450
A4:#N/A
A5:600
A6:9999
A7:9998
A8:10000
I want to check whether the range consists 9999 or not. If yes then 1 else 0.
I thought of an idea to use B1=10000 & C1=9998 so that 9999 is 'trapped' in between as <10000 (<b1) &="">9998 (>C1).
Another idea would also be welcomed. Result/goal is same.

Try...

=(COUNTIF(A1:A8,9999)>0)+0
</b1)>

 

[hsandeep]

This works.

 

[Aladin Akyurek]

This works.

Right. Thanks for providing feedback.