Formula for the word "between"
 

Hi, I'm new here, hope you can help.
I am struggling with trying to test if a value is between two others with
the IF statement
A value between two others would be TRUE, so
If TRUE then 0
if FALSE then test if it larger or smaller
If smaller then -1
If larger then +1

I can't seem to get the whole thing off the ground, keep going in circles
Please help, getting dizzy

Thanks

As an example

=IF(A1<1000,-1,IF(A11000,1,0))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ufo_pilot" wrote in message
...
Hi, I'm new here, hope you can help.
I am struggling with trying to test if a value is between two others with
the IF statement
A value between two others would be TRUE, so
If TRUE then 0
if FALSE then test if it larger or smaller
If smaller then -1
If larger then +1

I can't seem to get the whole thing off the ground, keep going in circles
Please help, getting dizzy

Thanks

With the target range as 1000 to 1010 inclusive then:

=-SIGN(A1<1000)+SIGN(A11010)

will give the returns required. But Bob's formula, (with a range), is more
intuitive.


--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"ufo_pilot" wrote in message
...
Hi, I'm new here, hope you can help.
I am struggling with trying to test if a value is between two others with
the IF statement
A value between two others would be TRUE, so
If TRUE then 0
if FALSE then test if it larger or smaller
If smaller then -1
If larger then +1

I can't seem to get the whole thing off the ground, keep going in circles
Please help, getting dizzy

Thanks