This Excel Factor tutorial was sent in by Bryon Smedley of Bristol, Tennessee.
Suppose you receive a spreadsheet with calculations, but the calculations are stored as text, for display purposes only, and not used to actually derive answers.
Without wanting to reinvent the wheel, you decide to try and use the documented versions of the formulas as actual number crunching tools.
Let’s set the stage for how this is accomplished.
The spreadsheet looks something along these lines:
Since there are no equal signs in front of any of the equations, Excel interprets them as simple text strings.
If you would like to know the answers to these equations, but retain the original text in Column A, a common approach would be to use the CONCATENATE function to produce the following functions in Column B:
 |
OR |
 |
Unfortunately, this does not work and yields the following result.
Because the original data was seen as text, the CONCATENATE function does not attempt to perform any sort of calculation and merely connects the two text strings together.
This is where an old, forgotten Excel 4.0 function comes into use: the EVALUATE Function.
EVALUATE is an Excel v4.0 macro function which is still packaged and supported in Excel 2010. The EVALUATE function allows for the evaluation of a text equation as an algebraic equation.
The odd thing about the EVALUATE function is that it cannot be used directly in a cell, like SUM or AVERAGE. The function can only be utilized with the confines of a NAMED RANGE. If you attempted to use it in the following manner, =EVALUATE(A1), you will be presented the below error message.
The trick is to program the EVALUATE function as a NAMED RANGE and then call the name from the target cell. This is how the process works:
- Select cell B1
- Go to the FORMULAS tab and click Define Name
- In the New Name dialog box, type the name Result (this can be any valid range name you like)
- In the Refers to: box type: =EVALUATE($A1)
- Click Add then OK.
It is very important to note that you select cell B1 and used a Relative Row reference for $A1.
Now enter =Result into cell B1 and copy it down for the remaining cells.
 |
 |
 |
|
(1) First Pass |
(2) First Answer |
(3) Fill Down |
Keep in mind, since this is actually an embedded Excel macro, you must save the file as an Excel Macro-Enabled Workbook (.XLSM) file.
USING THIS IN EVERYDAY SITUATIONS
Now that we understand this new (albeit old) power, how can it be used in more typical scenarios?
Excel 2007 introduced us to an improved version of Tables which have this wonderful ability to create formulas on the Totals line with convenient drop-down lists.
That’s great and easy, but what if we want that same drop-down functionality in a traditional table, or we want to have the drop-down located at the top of the data (or anywhere for that matter?)
Excel 2007/2010 Data Tables function selection drop-down
Data Table functionality at top of sheet
|
We want to be able to go from one set of formula choices… |
|
|
…access a drop-down list of alternate choices… |
|
|
…and dynamically create a new summary of calculations. |
|
The steps are the same as outlined above, but in this case we need to distinguish between the Cost (column "G" data) calculation and the Sales (column "H" data) calculation.
To simplify matters, we will add the entire column so the user can add data to the table and the calculations will still work.
|
Place your cursor in cell L5 and create the following Named Range |
 |
|
Place your cursor in cell L6 and create the following Named Range |
 |
IMPORTANT: Because we will not be filling this functionality down cells, and each cell points to a different data range, the reference pointers must be completely absolute. $-signs in front of the column letter and the row number.
The final step is to type =Cost_Result in cell L5 and =Sales_Result in cell L6
Enter your email address below to download the sample workbook.
Thanks again, Bryon for putting in so much effort to write this tutorial and include loads of images to help us follow along. We appreciate you sharing your knowledge.
Bryon is from Bristol, Tennessee and has been teaching Excel since version 7 which was included with Office 95. He is currently a Technical Training Analyst for one of the largest coal companies in the world. His key responsibility is to conduct all Microsoft Office training, but in addition he also serves as a technical consultant for any and all projects involving Microsoft Office applications.
Bryon says “Excel is by far my favorite among all the Office applications due to the almost infinite number of situations it can be used. There literally seems to be no end to its usefulness.
My favorite Excel tools are difficult to narrow down. Without getting into third party add-ins, I would have to say PivotTables (especially with the addition of Slicers. WOW! Are those things awesome!!), the Text to Columns tool (what a time saver), and Macros.”
Vote for Bryon
If you’d like to vote for Bryon’s tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment to thank Bryon for taking the time to share his knowledge….or all of the above 🙂
Didson New Pereira says
Thank you very much for the well explained topic as well as the details in others questions below.
I tested this solution and it works like a charm for what I intend to do while I am using simple funtions, however, when I try to put on some matrix referencing formulas, or even quite a few conditionals, it starts to give me #value errors.
Other thing that would be really really awesome is if I was able to use this kind of funtion on other areas such as in imput formula for conditional dropdown lists in cells, or conditional formatting.
Would anybody know if there are limitations of arguments to use the Evaluate funtion, and if there is any way to implement it in lists or conditional formatting?
Thanks !!
Catalin Bombea says
Yes, there is a limit of 255 chars per string evaluated, if your formula text exceeds this limit, the Evaluate function will fail.
You can use it anywhere you like, but I’ve never seen this function in a conditional formatting formula, you can build very complex formulas without the need of Evaluate. However, you know best what scenario you have, if you feel it’s easier with Evaluate, then use it, the final result is what matters.
Catalin
JeteMc says
Thank You for taking the time to post this most helpful tip. I hope that you have a blessed day.
Kohlman says
Does this work on Excel for Office 365 on a Mac? I have version 15.32 and it looks like they have done away with the ability to have the referenced range ($A1 in the case above) be a formula.
I also wasn’t able to find the evaluate formula on my version of Excel.
Any thoughts would be appreciated.
Thanks!
Catalin Bombea says
Hi,
As described in this article, Evaluate is not a sheet function, it’s an Excel 4 macro function, that can be used in a defined name, not in a cell directly.
I am not aware if Microsoft still supports old macro functions for Mac 2016, but you can test that very easy: Type in cell A1 the value 1, in cell A2 type 2, then define a new name(TestEvaluate), with this formula in the Refers To field: =Evaluate(“=A1+A2”). In any cell, type this formula: =TestEvaluate . If this cell displays the correct result, you have support for excel 4 macro functions.
Kohlman says
Mac 2016 does not support Evaluate function. Terribly unfortunate.
I am using it in a Virtual Desktop now, and the Evaluate function is supported, and I was able to get the correct answer per your TestEvaluation idea above.
However, I am now trying to evaluate a named range that refers to =EVALUATE(Sheet1!L17) when the contents of cell L17 is (text) “=’\\apkvf01\tsredirect$\kohlman\Desktop\[Test 2.xlsx]Sheet1′!A4”.
I am trying to pull data out of a closed document, and I cannot open the document. It is part of a bigger project.
Thanks!
Mynda Treacy says
Hi Kohlman,
I’m pretty sure the EVALUATE function doesn’t work with closed workbooks. I say ‘pretty sure’ because there is very little documentation on this function since it’s not officially supported.
Mynda
Didson New Pereira says
Hey Kohlman,
Seems that what you’re trying to do can be accomplished by using power query. I was struggling with having to get data from several workbooks without needing to open them. Well, power query does just that!
Mynda Treacy says
Hi Didson,
Indeed, Power Query can do that and more, now. This post was written in 2012, before Power Query was around 🙂
Mynda
Dan says
I’m trying to use this method with a formula that contains a file path to an outside workbook. I can get your example to work using as you outlined but when I try the following I get a value error:
AVERAGE(IF(‘R:\CRC\AA-DL-UA Stopsale\DL Stopsale\[02-02-17 DL Stopsale.xlsx]Stopsale’!$C:$C=C2,IF(‘R:\CRC\AA-DL-UA Stopsale\DL Stopsale\[02-02-17 DL Stopsale.xlsx]Stopsale’!$D:$D=D2,’R:\CRC\AA-DL-UA Stopsale\DL Stopsale\[02-02-17 DL Stopsale.xlsx]Stopsale’!$Q:$Q,””)))
Catalin Bombea says
Hi Dan,
Have you tried with the other workbook open? From your formula, seems that the workbook is closed.
Catalin
Ronnel says
I used this to method to create a formula string to reference a cell in another spreadsheet.
=CONCATENATE(“=VLOOKUP(Q1,'[DriverLoans-“,A4,”.xlsx]Sheet1′!$A$1:$D$100,3,FALSE)”)
Where A4 is the name of the driver. It works fine as long as I go to a cell, enter the defined name that EVALUATES the above formula, then hit Enter. But I expected it to AUTO REFRESH each time the DriverLoans-[driver].xlsx is updated, without having double click or F2 (edit) the cell then Enter. At the least, I expected it to REFRESH each time I open the spreadsheet or manually refresh.
I even created a Macro to record a series of F2, then Enter, to mimic a manual update, but the Macro resulted in putting blank in the cells. Which is bizarre because manually doing it works. But recording the manual steps in a Macro causes it to put blank in the cells.
I already saved it as a .xlsm and turned all all automatic updates in the FILE OPTIONS menu.
Is there a way to make this automatically update or is there another way that allows for automatic update?
Catalin Bombea says
Hi Ronnel,
You can try adding a volatile function like Now(), these type of functions are recalculated each time. Adding NOW()*0 to your formula will not change the result of your calculations. If your formula is expected to return text strings, not numbers, then adjust it to your needs, to add a zero length string instead of a zero: IF(Now()*0=0,””,0)
Catalin
Chirag says
Hi Mynda
Very helpful trick for Dashboard. I am facing one issue, I have a name range for few cells and I wants to use this EVALUATE, however I am not able to get correct result. For Example, Range Name is MyRange for cell A!:A10 value of which is 1,2,3,…,10 I wants to calculate SUM,MAX,MIN,AVERAGE,COUNT – selection from Data Validation List
I created Name Range EVAL with following formula:
=EVALUATE(“=”&Sheet2!$B$2&”(“&MyRange)
I selected SUM from List, I wrote in “C2” as =EVAL and I got SUM result as 2 instead of 55 and if I write =EVAL in “C3” then I am getting SUM value as 3 instead of 55
I modified EVAL as follows
=EVALUATE(“=”&Sheet2!$B$2&”(“&MyRange&”)”)
But same result
Will you please help?
Thanks
Chirag
Catalin Bombea says
Hi Chirag,
Can you please upload a sample file on Help Desk? It will be very helpful to see what you did there and to understand where the problem is.
Thanks for understanding.
Catalin
Catalin Bombea says
Hi Chirag,
The Evaluate function will not accept a named range, you have to provide the reference as a text: A1:A10.
If you provide a named range instead of a text reference, excel will create your function for the A1:A10 range (which contains numbers from 1 to 10) like this:
{“=SUM(1)”;”=SUM(2)”;”=SUM(3)”;”=SUM(4)”;”=SUM(5)”;”=SUM(6)”;”=SUM(7)”;”=SUM(8)”;”=SUM(9)”;”=SUM(10)”}
There is a way to convert back the name to a text reference, but it involves another excel 4 macro function:
Use this in your EVAL name:
=EVALUATE(“=”&Sheet1!$B$2&”(“&REFTEXT(MyRange,Sheet1!$A$1)&”)”)
The REFTEXT function will convert your named range to a text, and the Evaluate function will be able to calculate the entire range, not only the implicit intersection:
This is what Evaluate function will receive from REFTEXT function:
{“=”&SUM&”(“&”A1:A10″&”)”}
This way, you will be able to use a dynamic range inside Evaluate function.
Cheers,
Catalin
Joe Mallaci says
Love this function, wish it were a regular function that allowed nesting.
However, Evaluate function does not seem to auto-calculate. Does anybody have a solution for this?
Joe
Catalin Bombea says
Hi Joe,
Use a simple trick: use a volatile function to force a recalculation every time excel recalculates, without changing the result of your calculations: =EVALUATE($A1) +NOW()*0
NOW()*0 will always be zero, so it will not affect your result, but this function is volatile, so it will be recalculated every time a cell changes.
Cheers,
Catalin
Martin says
Thank you very much. But I have this problem: If it is written A2+A3 in cell A1 (in your example) and I change value in source cell (A2 or A3 in your example), the value in B2 is not re-evaluated automaticly. I must press Ctrl+Alt+F9. I would like to make set of formula choices. But I dont want to press Ctrl+Alt+F9 after every change in source cells. Can you help me, please?
Thank you once more.
Martin
Catalin Bombea says
Hi Martin,
Is the calculation mode set to Automatic? Check this setting, can be found on Formulas tab, Calculation section, select Automatic from Calculation Options.
Let us know if this was the problem.
Cheers,
Catalin
Martin says
Hi Catalin, thanks for your answer. The calculation mode is set to Automatic. All of formulas are calculated automatically, only formulas with Evaluate function aren’t.
Best regards
Martin
Catalin Bombea says
Hi Martin,
A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. EVALUATE is a nonvolatile function, it will be recalculated only when you make changes to the text in A1, as it’s not directly dependant to A2 or A3!
In VBA, it’s easy, we can add Application.Volatile to make an UDF volatile:
Function EVALUATEString(ref As String)Application.Volatile
EVALUATEString = EVALUATE(ref)
End Function
You can try this version of VBA EVALUATE function. Use it in sheet cells like this: =EVALUATEString(A1)
But there is a way to make a nonvolatile function volatile: for this, we have to use a VOLATILE function (like NOW() function )in our defined name:
=EVALUATE(Sheet6!$A1)+NOW()*0
Of course, i had to multiply NOW with 0, to keep the original calculation intact.
Hope it’s clear 🙂
Regards,
Catalin
Martin says
It´s super trick! (…+NOW()*0).
Using VBA Function EVALUATEString helped me too.
Thank you very much, my problem is solved and I learned new knowledge of Excel.
Martin
Catalin Bombea says
You’re welcome Martin 🙂
Cary Bondoc says
Thank you so much for this, you really saved us!
Mynda Treacy says
You’re welcome, Cary. Glad we could help.
s says
Thank you. It was excellent and helped me much.
Mynda Treacy says
You’re welcome, S 🙂
MMI says
NICE THANKS,BUT SAME NAME IS NOT WORKING IN OTHER SHEET (MULTIPLE) WITH IN SAME WORKBOOK
Mynda Treacy says
Hi MMI,
I’m not sure what you mean. If you’d like to send me your workbook via the help desk I can take a look at the problem.
Kind regards,
Mynda.
MMI says
HI MYNDA
THANKS, FOR YOUR PROMPT RESPONSE.I WILL SEND YOU MY FILE.
Amirali Makan says
Could have never guessed! Has been of great help in my work. Thank you.
Ricardo Hernandez Tablas says
When you create formulas with text functions, and you added to the formula the equal sign, for example =25+34, like in the fourth figure that is in the post. Also works if you use command find and substitute de equal sign for another equal sign, then Excel will transform the text formula into a formula.
Carlo Estopia says
Hi Ricardo,
Nice Info right there.
Thank you.
CarloE
Kris says
Thanks, Bryon and Mynda!
For VBA developers only – you can read an interesting fact about Evaluate function and UDFs here:
https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell
Cheers,
Kris
Gordon de Beer says
Thanks, very informative to know about old formula quirks.
Often though I resolve this problem by doing the following:
1. Select the column that has the concatenated values such as =21*25
2. Do a Copy paste special values of selection
3. Data text-to-columns and finish
Thanks,
Gordon
Mynda Treacy says
Cheers, Gordon.
I agree, Text to Columns is a great tool and the one I would probably use to fix the formatting too.
Kind regards,
Mynda.
Bryon Smedley says
But doesn’t that remove the entire dynamic nature from the spreadsheet? Wouldn’t you need to re-Copy/Paste and re-Text to Columns every time the formulas changed?
If it was a single pass through the data, I can see your point, but my purpose was to provide a means of changing the questions (re: formulas) with no further work on the user’s part.
Have a great day!
Mynda Treacy says
Correct. I was considering a one-off change to fix the data but, if the intention is to add more data then your solution would be better. Good point.
Cheers.
Lupe Jau says
Thanks for sharing, I have been learning a lot about excel. God bless you
Sincerely
Lupe Jau
Computer Instructor
Mynda Treacy says
Thanks, Lupe. Glad you’re enjoying learning from our site 🙂
Ade says
Worth reading, thanks for sharing! 🙂