CODEWITHSUNDEEP
excelcastingexcel-formulaboolean-expression
sp00m
sp00m

Reputation: 48837

Casting a string to a boolean operator (<, <=, >, >=)

In cell A1, there can be either <, <=, > or >= (of string).

In cell B1, there's the threshold's value of int.

From A3, there are values of int.

+---+-----+----+
|   | A   | B  |
+---+-----+----+
| 1 | <   | 15 |
| 2 |     |    |
| 3 | 10  |    |
| 4 | 20  |    |
| 5 | ... |    |
+---+-----+----+

The value in A3 should be copied in B3 if and only if it respects the boolean operator (A1) and the threshold (B1). Otherwise, B3 should be #N/A. Of course, the same for A4/B4, A5/B5, etc.

In that example, B3 should be 10 and B4 should be #N/A, because 10 is less than 15, but 20 isn't.

Here is a formula I created which works great, but which is a bit long (this is for B3):

=IF($A$1 = "<"; IF($A3 < $B$1; $A3; NA()); IF($A$1 = "<="; IF($A3 <= $B$1; $A3; NA()); IF($A$1 = ">"; IF($A3 > $B$1; $A3; NA()); IF($A3 >= $B$1; $A3; NA()))))

The same in a more digest way:

=IF(
    $A$1 = "<";
    IF($A3 < $B$1;$A3; NA());
    IF(
        $A$1 = "<=";
        IF($A3 <= $B$1; $A3; NA());
        IF(
            $A$1 = ">";
            IF($A3 > $B$1; $A3; NA());
            IF($A3 >= $B$1; $A3; NA())
        )
    )
)

Is there any more convenient way to do that job? Casting from a string to an operator would be great, but yeah, I dunno any programing language capable of doing such kind of stuff :) Ideally, it should be something like =IF($A3 $A$1 $B$1; $A3; NA())...

Upvotes: 1

Views: 2227

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

Try this formula

=IF(COUNTIF(A3,$A$1&$B$1),A3,NA())

Upvotes: 4

RocketDonkey
RocketDonkey

Reputation: 37269

Note that this is based completely on this article, but it seems like it would fit here. There are two ways you can do it: using a named range and a UDF. Both ways basically take advantage of Excel's 'hidden' EVALUATE function.

Named Range

To use a named range, you define a name (in this case, I used Compare) and assign it the following value (note this is all relative the ranges in my sheet, so you may need to adjust):

=EVALUATE("IF(" & A3 & $A$1 & $B$1 &"," & A3 & ",NA())")

Note that the ranges appear to automatically update with the sheet name prefixed.

enter image description here

UDF

Add the following as a UDF:

Function Evalu(ByVal S As String) As String
  Evalu = Evaluate(S)
End Function

And then enter the following formula:

=INT(IFERROR(Evalu("IF(" & A3 & $A$1 & $B$1 &"," & A3 & ",NA())"),NA()))

The double NA()'s are a bit hacky, but the Evalu function returns a #VALUE error instead of #N/A, so this clumsily forces the error you want. Also, the function returns a string, so you need to cast the result as an integer. I somewhat prefer the first method :)

enter image description here

Upvotes: 1

Related Questions