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
Download the .xlsm example file.
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 