Formula for if a value is above, below or within a range

Hi, I am analysing a large data set. I basically need to know if a value is below X, above Y or between X and Y. And depending on which of these is the case, a different value will appear. I have three columns as shown below. Columns 2 and 3 represent a range (Value x to value y)

Column 1 Column 2 Column 3 Column 4
Row 1 3 4 7 -1
Row 2 5 3 6 0
Row 3 6 2 5 1

I want the formula to give a value of -1 if the value in column 1 is lower than the value in column 2 (As is Row 1)
I want the formula to give a value of 0 if the value in column 1 is equal to or between the values in column 2 and column 3 (As in Row 2)
I want the formula to give a value of +1 if the value in column 1 is greater than the value in column 3 (As in row 3)

Basically if the value in below the range -1, within the range (including the values of the range) 0, and above the range 1

If anyone can help with this i would REALLY REALLY appreciate it as I am at a wall now and cant make sense of it!

Thanks

Thank you Tsjallie! I will try that right now!

It Worked!! Thank you so so much

Originally Posted by Tsjallie

This should just do that:

Formula:

Please Login or Register  to view this content.

This worked for me, thank you so much!