0

I'd like to setup a spreadsheet that would let the user select the operator to apply to other cells. For example: If A1 has 5 and A2 has 10, I'd like the user to be able to select the plus or minus operators from cell A3 (resulting A4 displaying the result of: A1 + A2 or A1 - A2).

I know I could do this with a IF statement or CHOOSE. But I was wondering if there was something shorter. I've tried setting A4 to these options but none work as intended. I want a numerical result, such as 15 or -5

Here's what I've tried: - =A1&A3&A2 - =A1+A3A2

Are there any methods other than IF or CHOOSE?

user348514
  • 743

1 Answers1

0

There's two parts to the answer to this question ...

  1. Building a proper string to evaluate, answered here
  2. Evaluating that string as if it were a real formula, answered here

An example of building the correct string, for the problem you laid out: In Cell A4, put the following formula ...

=A1&A3&A2

If you put 10 in A1, 11 in A2, and * in A3, this will evaluate to 10*11

More complex ways of building the string may require use of the ADDRESS and/or INDIRECT functions.

Older versions of Excel carried around a UDF for Evaluate, that does not exist in more recent versions. You would need to create your own UDF such as ...

Function myEval(r As Variant) As Variant
    If TypeName(r) = "Range" Then
        myEval = Evaluate(r.Value)
    Else
        myEval = Evaluate(r)
    End If
End Function

Following the example, in Cell A5, you could put ...

=myEval(A4)

which would evaluate to 110. Or you could replace the contents of Cell A4 with ...

=myEval(A1&A3&A2)

which wold then evaluate to 110.

Community
  • 1
OldUgly
  • 363
  • 1
  • 7