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?

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Hysteresis​

[/td][td]

3​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td="bgcolor:#F3F3F3"]

Setpoint​

[/td][td]

70​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td="bgcolor:#F3F3F3"]

Temp​

[/td][td="bgcolor:#F3F3F3"]

Heat​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

70​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td]B6: =IF(A6 >= $B$2, 0, IF(A6 <= $B$2 - $B$1, 1, B4))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

69​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

68​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

67​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

68​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

69​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

70​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

13​

[/td][td]

69​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

14​

[/td][td]

68​

[/td][td="bgcolor:#E5E5E5"]

0​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

15​

[/td][td]

67​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

16​

[/td][td]

68​

[/td][td="bgcolor:#E5E5E5"]

1​

[/td][td][/td][/tr]
[/table]