Dynamic Comparator?

[CameronR]

Is there a way that you can reference another cell that contains a comparator (< , > , <= , etc) in a formula and use that referenced cell as the comparator as a in an IF statement?

[XOR LX]

Hi,

Assuming that A1 and B1 contain your values (presumed numerical) to be compared, and C1 contains one of the comparators "<", "=" or ">", this formula will return TRUE if the relationship of A1 to B1 with respect to the comparator in C1 is true:

=IF(SUMPRODUCT(COUNTIF(C1,"*"&{"<","=",">"}&"*")*(SIGN(B1-A1)+{0,1,2}=1)),TRUE)

(courtesy of mik erickson)

Regards

[ben_hensel]

I did something similiar to that; I couldn't figure it out how to directly feed it the value, so I did something circuitous:

Ran a double nested IF() to see whether A = B and then whether A > B to deliver 1, 2, or 3; and then used 1,2,3 and the input logical operator as the axises of a lookup table to deliver a T/F to another IF.

It's pretty kludgy, but I think it completely covers all possible bases???

[CameronR]

Thanks! I tried both of the above and they work. I like the first solution the best because you dont need to set up any truth tables which is important when comparing large amounts of data