Comparison Operator with Bias

[XeonTao]

I was going to quickly mock up some logic in excel for another project I'm working on and ran into a problem I cant solve without creating dependency loops.
I'm looking for a less than comparison with a bias:

=IF(X1"<"Y1,1,0)

and would remain true until:

=IF(X1">"Y1+B1,1,0)

Here is as close as I could get, but of course its a direct dependency loop. (A1 being the cell with the function)

=IF(A1=0,IF(X1"<"Y1,1,0),IF(X1">"Y1+B1,1,0))

I could do the same thing in VB, but it seems like it should be simple function.
(Sorry, double quotes added around comparison operators because it wouldn't display properly without them)

 

[joeu2004]

Sorry, double quotes added around comparison operators because it wouldn't display properly without them

In the future, type spaces around comparison operators instead of double-quotes. It is confusing when you write incorrect syntax because we don't know if that might be part of the problem.

(Sorry, but I don't understand your primary question well enough to offer a solution.)

 

[XeonTao]

The code doesn't help explain what I'm looking for at this point.
What I mean is, if x is less than y I want to function to be TRUE until x is greater than y+bias.
Or for a real example, a low limit situation, If the thermostat is less than 40 degF it would alarm and would stay in alarm until the thermostat is greater that 40 degF + 2 degF(bias).

The problem is, when doing it in a formula, the cell would have to know what state it's in, creating the circular reference.

 

[shg]

You mean hysteresis?

	A​	B​	C​
1​	Hysteresis​	3​
2​	Setpoint​	70​
3​
4​	Temp​	Heat​
5​
6​	70​	0​	B6: =IF(A6 >= $B$2, 0, IF(A6 <= $B$2 - $B$1, 1, B4))
7​	69​	0​
8​	68​	0​
9​	67​	1​
10​	68​	1​
11​	69​	1​
12​	70​	0​
13​	69​	0​
14​	68​	0​
15​	67​	1​
16​	68​	1​