32

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?

This would be helpful for another project where I would like to be able to write 'dynamic' formulas in Excel.

  • 3
    excel had this EVALUATE() function that does exactly this. That was a long time ago though, and i am not too sure about the new excel. Will poke around and see if i can find something equiv. – aCuria Mar 5 '11 at 1:12
  • 1
    that function sounds familiar, but I certainly can't find it in Excel2007, which is what I'm currently using. – drapkin11 Mar 5 '11 at 1:15
  • I cant find it either =/ – aCuria Mar 5 '11 at 1:17
28

EVALUATE is available in VBA in all current versions

You can include it in you VBA code, or wrap it into a simple UDF to make it available as a worksheet function

Function ev(r As Range) As Variant
    ev = Evaluate(r.Value)
End Function

It basically treats the value of the passed parameter as an Excel formula, same as if it were entered in a cell

"11+5" and "=11+5" will produce the same result

| improve this answer | |
  • 1
    I forgot all about user-defined functions in Excel -thanks. – drapkin11 Mar 5 '11 at 3:21
  • I did a small modification changing Range parameter to String and it works nice form me. thanks – Makah Jan 20 '14 at 20:26
19
=evaluate(put_reference[s]_here)

This is a semifunction - it can only be used in Name Manager.

This is how you can use it:

  • Point to a cell and you open Name Manager (From the FORMULAS tab or by clicking CTRL+F3)

    Evaluate Example

  • Write =evaluate( and click on the cell you want (best to keep relative reference).

  • Finish the formula with )

  • Give it a NAME - (in this example I'll just call it eva).

  • Click OK.

Now, let's suppose that you've selected B1 and made all this refer to A1. In A1 you can put "1+1" and in B1 you write =eva - once you've hit ENTER, the B1 value will be 2. As the reference in Name Manager was relative, you can use =eva to get the evaluation of any cell one cell left from where you want it. (eg. in B2, =eva will return the result of cell A2)

| improve this answer | |
  • 1
    Very good - and nice to know. It even works for a table column: =EVALUATE(Tablename[@[column]]) Something you can't find in an most Excel Help- or advanced Tutorial easily – Paschi Oct 11 '17 at 20:13
  • 1
    ... and as usual with excel formulas, if you use a localized build, you need to use the localized function - e.g. in Excel 2016 DE it's called =auswerten(...) – kiwiwings Sep 4 '18 at 13:28
7

There is an important caveat with the great answer from @karel and @Laurentiu Mirica: the evaluate function won't recalculate unless the referenced cell changes. For example, cell C1 contains the text "A1+B1" and D1 contains the function =eval. If the values in A1 or B1 change, the cell D1 does not get recalculated.

Demonstration of eval problem

This can be corrected by introducing a volatile function into either the string or the eval cell. This will force a recalculation every time the worksheet is recalculated. For example, cell C1 could be replaced with =if(today(),"A1+B1",). Or, D1 could be replaced with =if(today(),eval,). Any volatile function should do.

A third and perhaps the simplest solution is to change semi-function in the name manager to =if(today(),evaluate(c1),)

| improve this answer | |
  • Thanks I have been looking for a trick like this of and on for some time :) – Ben Personick Feb 15 '19 at 16:22
5
=indirect()

if you use this in a cell (alongside concatenate) it can be very useful.

For example, this formula will display the value of cell B5 on another worksheet (the name of which is stored in cell A2 on this worksheet):

=INDIRECT(CONCATENATE(A2,"!B5"))

To make INDIRECT work the external worksheet must be open.

| improve this answer | |
  • Doesn't work, returns #REF – SIslam Jan 11 '16 at 5:29
  • 10
    INDIRECT can perform math and functions, but only as part of creating a cell reference. It can't be used in the general sense asked in the question. – fixer1234 Sep 7 '16 at 16:14
1

Enhanced Function to execute a string as though it is a formula. Function extended from above ev() function.

New Function Name: EvaluateEx()

  • Paste function to an Excel VBA Module.
  • Place function name into a worksheet cell.

Example Calling Syntax: * =EvaluateEx("=11 + 5") * =EvaluateEx("=g1 + g2")+ evaluateEX("2 + 3") * =EvaluateEX("defined name")

Function tested with:

  • Structured Table References
  • Defined Names that reference text,
    filter() function, etc.

    Function evaluateEx(r As Variant) As Variant
    'Note: Renaming function requires same change after the ExitFunction label.
    'User Function to evaluate string as formula.
     Dim ev As Variant
    
       Select Case TypeName(r)
    
           Case "Range"
                If r.Value <> vbNullString And Trim(r.Value) <> "=" Then
                   ev = Evaluate(r.Value)
                Else
                   ev = r.Value
                End If
    
           Case "String"
                If r <> vbNullString And Trim(r) <> "=" Then
                   ev = Evaluate(r)
                 Else
                   ev = r
                 End If
    
           Case "Variant()"
                 ev = r
    
           Case "Double"
                 ev = r
    
           Case "Error"
                 ev = "Defined Name not found in list of Defined Names. Cannot Evaluate"
    
           Case Else
                 ev = "Unknown parmeter type. Cannot Evaluate"
        End Select
    
        On Error GoTo ExitFunction 'Handle possible type mismatch
        If ev = CVErr(2029) Then
                 'ev = "The parameter passed to the EV function results in a value (i.e. " & r & ") that cannot be evaluated by the EV function."
                  ev = r
        End If
    
    ExitFunction:
    
        evaluateEx = ev
    
    End Function
    
| improve this answer | |
0

jlear's answer is INCORRECT regarding Laurentiu Mirica's answer using the Excel 4 Macro function EVALUATE() in a Named Range: it will recalculate EVERY TIME one of the inputs changes. Which makes it a wonderful solution to exactly this kind of problem.

Of course, one can simply use A1 and B1 in the formula, if that is where the values are. It is NOT required that everything be numerals in a string in a single cell. Though it works for that too.

| improve this answer | |
New contributor
jeorje is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.