A number to check within a range of 2 nos

[hsandeep]

I need to judge whether a number falls between a range of 2 numbers.
Ex:
A1=332.024 B1=349.956
C1=335.23 So D1 must give 1 (since between the range) else 0.

If C1=332.02 D1=0
If C1=349.9566 D1=0
If C1=332.024 D1=1
If C1=349.956 D1=1

What formula should be punched in D1?
Thnx in adv

 

[Greg Truby]

=(c1>=a1) * (c1<=b1)

 

[Weazel]

=sign(and(c1>=a1,c1<=b1))

 

[hsandeep]

Thanks. IT WORKS. I am using Weazel's. Is it better then Greg Truby in what sense?

 

[Greg Truby]

Thanks. IT WORKS. I am using Weazel's. Is it better then Greg Truby in what sense?

I designed mine for good calc speed. Weazel's takes about 30% longer to run. Both yield the same results. Now, having said that - that 30% is really peanuts. I have a 5-year-old notebook with just a dual-core processor and the difference was 510 milliseconds versus 666 milliseconds on 1,000,000 rows (average of three calc runs each using FastExcel). So, unless you're cranking this on either a really, really old machine or a really, really huge number of rows, either formula will yield satisfactory results.

 

[hsandeep]

I'll revert back with my Comp's hardware details soon.

 

[hsandeep]

If i further expand the range
A1=332.024 B1=349.956 C1=400.997

If D1=335.62 E1=1
If D1=332.024 E1=1
D1=349.956 E1=1
D1=349.957 E1=2
D1=400.998 E1=0
D1=332.001 E1=0

Which formula to be used in E1?

 

[Greg Truby]

I'm sure one of our power formula gurus will stop by before long and offer up something much more elegant than my hacked solution. But this should work:

• =IFERROR(LOOKUP(D1-(0.000000000001*(D1<>A1)),A1:C1,{1,2,0}),0)

 

[hsandeep]

Yes it WORKS.