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 MacroEnabled 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 dropdown lists.
That’s great and easy, but what if we want that same dropdown functionality in a traditional table, or we want to have the dropdown located at the top of the data (or anywhere for that matter?)
Excel 2007/2010 Data Tables function selection dropdown
Data Table functionality at top of sheet
We want to be able to go from one set of formula choices. 

access a dropdown 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
Check out 60+ Essential Formulae here through suitable examples, required to perform the job efficiently and effectively.
Happy Excelling – Team Excelgoodies – www.Excelgoodies.com