Excel - Execute formula stored as text

Question

Hello,

In Excel, I would like to know if it is possible to execute a formula written in another cell as a simple string of characters within a cell.
This way, it would be possible to replace an endless "IF" formula (else IF, else IF, etc.) with a lookup in a matrix of formulas, depending on the case at hand.

For example - Let's assume a sheet that converts measurement units (Celsius to Fahrenheit, gallons to liters, etc.):
- A "Conversion" tab contains two editable cells: A1="Value to convert" and A2="Conversion to apply" and a non-editable cell A3="Result".
- A "Conversion formulas dictionary" tab contains two columns A:FormulaName and B:Formula. An example - in A2 there would be "Inch->Cm" and in B2 it would be "'=Conversion!$A$1*2.54
- In the cell Conversion!A3, is it possible to use the formulas from the "Formulas dictionary"???

Another example to simplify my question further:
- Cell A1: '=IF(B1=1;"True";"False") (Note the apostrophe before the formula which makes it a plain text string)
- Cell B1: 1
- Cell A2 ???Execute formula written in A1??? : True

Thank you!

Configuration: Windows XP Internet Explorer 6.0

eriiic · Accepted Answer

Hello everyone,

It is indeed possible to do this without VBA but with a trick.
I'm sharing this as information (VBA offers more flexibility), but for a one-time need, it can be useful, especially if you're not comfortable with VBA.

In an older version of Excel (Excel 4?), there was the function =EVALUATE(A2) that allowed you to calculate the result of the function written as TEXT in A2. For example, A1: 5 A2: '=A1*2.54

The function (more documented) has been disabled. If I enter in A3: =EVALUATE(A2), I get 'invalid function'.

However, by defining a name, you can get around this.
Menu 'Insert / Name / Define...'
Name: function_variable
Refers to: =EVALUATE(Sheet1!$A$2)

In A3, I enter the name: =function_variable and I get the result of '=A1*2.54', which is 12.7

I haven't looked in detail to see if it's easily applicable to your case because you already have the VBA solution from gb, but it was an opportunity to remind you of this trick.
Eric

gbinforme · Answer

Hello  In Excel, I would like to know if it is possible to execute, in one cell, a formula that is written in another cell as a simple string of characters.  The answer is yes and I will give you an example of how to do it: https://www.cjoint.com/?deaY7Hjvod  However, one must be careful about the syntactical writing of the formulas, which must be error-free.  It would thus be possible to replace a lengthy "IF" formula (otherwise IF, otherwise IF, etc.)   When we are overwhelmed by "IFs", it means that the organization of the data needs to be reviewed or that we should try to think about other functions that are easier to maintain, such as indeed a lookup in a result matrix.  --  Always zen

Dav · Answer

Excellent little trick! Thank you Eric.
By combining it with VLOOKUP and an indirect reference (INDIRECT), I am able to add the necessary flexibility.
(I now remember this function from earlier versions of Excel... and I imagine it was removed for security reasons.)

tracker54 · Answer

Hello,

Dav wrote:
Excellent little trick! Thanks Eric.
By combining it with VLOOKUP and an indirection (INDIRECT), I can add the necessary flexibility.
(I now remember this function from earlier versions of Excel... and I imagine it was removed for security reasons.)

Could you enlighten me on the method with VLOOKUP and INDIRECT function?

Thank you in advance,

eriiic · Answer

Hello everyone,  For everyone to benefit, it is possible to upload the file on cijoint.fr and paste the provided link here. eric

tracker54 · Answer

Hello, here is the link to the file, http://www.cijoint.fr/cjlink.php?file=cj200907/cijUvjA1nG.xls -- If my aunt had one, we would call her my uncle...

Dav · Answer

Hello,  Here is the example I was talking about yesterday. http://www.cijoint.fr/cjlink.php?file=cj200907/cijSXOd6ui.xls

eriiic · Answer

Hello,

Nice sample file, Dav

Yes, it is indeed an Excel 4 macro. In this version, macros were formulas that were entered one below the other in a sheet.
And even if some functions like delete.file() could be considered dangerous, they don't seem to work in other Excel versions. Very few of these functions are reusable, so we can accept these macros.
In any case, solutions often require VBA, which also allows for a lot of things...
A reliability indicator could be the number of messages posted on the forum by the person providing you with the file.
Eric

youssef · Answer

Hello
it's even possible to do it like this:
for example, define a name "test1" as follows: =EVALUATE(DIM)
where DIM is also the name of a range of cells in a column. Thus, for each row, "test1" will return a value according to the value of the cell in the "DIM" row.
it's nice so you don't have to define a name every time for a task that will be repeated.
best regards.

Excel - Execute formula stored as text

9 réponses

Saracroche: the effective french app against phone spam

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress

Inuyasha in french