0

So I could really use a modular operator in one of my Excel cells, just to try out different things and mess around a bit.

So in the one cell there'd be for example =A1(operation in B1)A2

Thus when I'd write a "plus" ('+') sign in B1 I get an addition, but if I write a "star" ('*') I get a multiplication.

Same could go for =(name of function in B1)(A1; A2)

And in B1 I could write either MIN or MAX.

Of course I could do it with IFs, but my question is about building a cell formula with text from another cell and then interpreting it (thus not limiting myself to a few IF cases). Just like INDIRECT does, but not just with cell references. Any idea ? Thanks in advance.

Charles
  • 988
  • 1
  • 11
  • 28

1 Answers1

1

I think you need to be consistent in your approach: either a function name, or an operator, but not both (+ and * can be replaced with SUM and PRODUCT respectively, so this is not an issue).

If you go to Name Manager and define a new name, Operation say, as:

=EVALUATE($B$1&"("&$A$1&","&$A$2)

(Replace the "," with ";" if your version uses the semi-colon as argument separator in formulas and not the comma.)

Exit Name Manager.

Then, in any cell:

=Operation

Test with various strings in B1, e.g. SUM, PRODUCT, MAX, MIN. Of course, this will only work with functions whose syntax is of the form:

=FUNCTION(Arg1,Arg2)

And it also requires that you save the workbook as macro-enabled.

Regards

XOR LX
  • 7,632
  • 1
  • 16
  • 15
  • Thank you ! The two relevant infos in your answer were : use EVALUATE, and use it in a name. That's all I needed ! – Charles Jun 02 '14 at 10:20
  • But, why in a name, and not directly in the cell formula ? – Charles Jun 02 '14 at 10:21
  • It's an old Macro-Enabled function which for whatever reason Microsoft decided to keep in later versions with the caveat that it must be done via a Named Range. I don't know the technical reason why. – XOR LX Jun 02 '14 at 10:40
  • Well, thanks a lot anyway. Question answered ! I'm happy. It even works with cell extension in line or column - pretty powerful ! – Charles Jun 02 '14 at 11:37