1

I want to use the value of a cell which is a logical operator (>,≥,<,≤) in a formula to calculate another cell.

For example an IF statement based on a logical operator:

A1: 5
A2: 3
A3: >
A4: 4
A5: =IF((A1-A2) ??A3?? A4;1;0)

By what do I need to replace ??A3?? to make this work?

Johannes
  • 115
  • 15

2 Answers2

1

The only way I can think to do this would be to encode formulas for each type of comparsion operator. For example, if you wanted to support both < and > in the following formula:

IF((A1-A2) OP A4, 1, 0)

then you could include formulas for both less than and greater than, and choose one based on A3:

IF(A3="<", IF((A1-A2) < A4, 1, 0), IF(A3=">", IF((A1-A2) > A4, 1, 0), -1))

In the formula immediately above I return -1 should A3 contain an operator which we did not expect (not < or > in this simple example).

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • That's not the nicest solution, but certainly one that works. Thank you :) just as a note, you wrote A4 instead of A3 twice, and my Excel complains if I use ' and , -> so I need to use " and ; `=IF(A3="<"; IF((A1-A2) < A4; 1; 0); IF(A3=">"; IF((A1-A2) > A4; 1; 0); -1))` – Johannes Oct 13 '17 at 13:16
0

You can use something like the formula below:

=NOT(ISERROR(FIND(A3, MID("<≤≥>", 2 + SIGN((A1-A2) - A4), 2))))

So, generally speaking, your formula will be the following:

=NOT(ISERROR(FIND(#OPERATOR#, MID("<≤≥>", 2 + SIGN(#OP1# - #OP2#), 2))))

The benefit is that you need to specify the numbers that you're comparing only once (so can you can have any arbitrary long formula for these numbers, without worrying that you're duplicating it).

This works by mapping the symbols <, ≤, ≥ and > to the numbers 1, 2, 3, 4 and then comparing this against the sign of the difference of the initial numbers.

Razvan
  • 2,436
  • 18
  • 23