CODEWITHSUNDEEP
excelformula
Charles
Charles

Reputation: 1229

Excel formula out of cell text

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.

Upvotes: 0

Views: 217

Answers (1)

XOR LX
XOR LX

Reputation: 7742

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

Upvotes: 1

Related Questions