Secret Evaluate Function

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:

Excel EVALUATE function

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:

Excel EVALUATE function

OR

Excel EVALUATE function

Unfortunately, this does not work and yields the following result.

Excel EVALUATE function

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.

Excel EVALUATE function

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:

    1. Select cell B1
    2. Go to the FORMULAS tab and click Define Name

;

Excel EVALUATE function

    1. In the New Name dialog box, type the name Result (this can be any valid range name you like)
    2. In the Refers to: box type: =EVALUATE($A1)

;

Excel EVALUATE function

  1. 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.

Excel EVALUATE function Excel EVALUATE function Excel EVALUATE function

(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

Excel EVALUATE function

Data Table functionality at top of sheet

Excel EVALUATE function

We want to be able to go from one set of formula choices.

Excel EVALUATE function

access a drop-down list of alternate choices

Excel EVALUATE function

and dynamically create a new summary of calculations.

Excel EVALUATE function

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.

Excel EVALUATE function

Place your cursor in cell L5 and create the following Named Range

Excel EVALUATE function

Place your cursor in cell L6 and create the following Named Range

Excel EVALUATE function

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

Excel EVALUATE function

Check out 60+ Essential Formulae here through suitable examples, required to perform the job efficiently and effectively.

Happy Excelling – Team Excelgoodies – www.Excelgoodies.com

Leave a Reply

Your email address will not be published. Required fields are marked *