exceltoxl.com

Hi there,

Today, I am forced to learn about a new function in excel. Basically, it is not new but very few will know/use this function. The function name is EVALUATE. This is basically a macro function which is available in Excel 4.0 version. You can also see an alternative functions like this in my previous posts, in List FileNames in a Folder and Count Worksheets in a Workbook. 

EVALUATE function have a flavour of INDEX function. Not exactly but just a flavour. This function works in macro enabled workbooks only. Let's see why we use and how to utilize this function on our requirements on day-to-day excel works.

evaluate-function-macro-enabled-function.mp4

Sometimes, when we write a formula and to display it to the audience to know which formula we have used we will add ' (single apostrophe) in the start of the formula. So that it will not show the results but display the complete formula. Say, we have few calculations mentioned in a cell without giving = (is equal to) symbol, like 

...

Here we can convert above references to calculations by using CONCATENATE formula.

We will not achieve the results as expected using CONCATENATE because the references given above are in text format. So it just display's like this.

This is where the header of this post come's in place. Yes, EVALUATE.

We need to take a precaution here is that, we should not use EVALUATE function directly to the cell reference to convert references to results. If you try to do, this is the error which pop's up infront of our eyes.

HOW TO AVOID THIS ERROR?

Let me tell you this function doesnót work like other routine functions. We need to use this function in NAMED RANGES. ( I have used named ranges feature while creating DYNAMIC CHARTS)

CREATING NAMED RANGES:

In short, FORMULAS--> DEFINED NAMES-->DEFINE NAME-->NEW NAME-->

NAME = CALC1 and REFERS TO = "=EVALUATE(G3)"

Now, use CALC1 to calculate these raw references like this.