Hi Roger,

As you know, SUMPRODUCT has evolved to something beyond the original creator's imagination. Guess this has not happened to EVALUATE! I wish we could use EVALUATE to put the result of a function in a cell temporarily, until the series of functions are completed.

e.g. If INDEX(MATCH( = not found, NA( ), INDEX(MATCH(

As you see, INDEX(MATCH is performed for a second time if a match is found. If we can use EVALUATE to store the result of the first trial in a cell, we can just plug in the result if a match is found. Wonder if this is possible. If we name the INDEX(MATCH( portion of the formula, will speed be improved? Probably not.

The only alternative I can think of is to use COUNTIF for the logical test for a match. This should be faster than INDEX(MATCH(.

Hope you understand what I am after. Thank you for reading.

Epinn

"Roger Govier" wrote in message ...
Hi Epinn

Why is z = 0% and not 100%?
My mistake. It should have been set to 1 in this scenario (or 100%). In
other cases where I am calculating the VAT amount (starting from the Net
of VAT figure), then it would be 0%. I apologise for the confusion in
typing in this example.

It is acceptable to key in =Gross*s in a cell but not =Gross*VAT_Rate
even though VAT_Rate returns "s."

Yes, this is exactly the problem and why EVALUATE is needed as an
intermediate step.
Let's say Vat_Rate of "s" was held in cell D2 and Gross in E2, even
E2*INDIRECT(D2) returns a #REF error.
It was my original attempt to use IINDIRECT and its failure to operate
as I had hoped that led me to discover the use of Evaluate to carry out
the intermediate calculation.

--
Regards

Roger Govier


"Epinn" wrote in message
...
Roger,

I can't thank you enough. Your example is such a beauty and I am
inspired. I always find Excel challenging in the sense that we
sometimes have to put together so many functions as one long formula.
You have no idea how many times in the past I wished I could have a few
short formulae in one cell. Now that I have seen some practical use of
named formulae and Evaluate, I think it can be done.

I set up the worksheet and things become quite clear.

The only row that is "fixed" is the header. It will still be okay if we
insert rows above it but we can't move (cut and paste) it to another row
without changing the definition.

It is acceptable to key in =Gross*s in a cell but not =Gross*VAT_Rate
even though VAT_Rate returns "s." We have to use Evaluate to fix the
#VALUE! error.

I have a question on the tax calculation. Why is z = 0% and not 100%?
If "z" means no VAT is required, then net should equal to gross, and
100% can make this happen. Explanation is optional as the purpose of
this exercise is to learn Evaluate and not VAT. ;)

Thanks again.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

the rows are "fixed"
No, as you drag the formula down the page, the formula is relative to
the row upon which it resides.
What I am saying, is, the columns are not "fixed" in that it does not
matter if you move their location on the sheet by inserting other
columns before them or after them.
There is no question about having to alter via InsertNameDefine

Try setting up a sheet for yourself in this way and you will see what I
mean.
--
Regards

Roger Govier


"Epinn" wrote in message
...
Hi Roger,

Thank you very much for sharing. This is what I call "generosity."
It's great to see a real life example especially when there is nothing,
absolutely nothing, on Excel Help.

All of these formulae are relative to the row being used on the
sheet,
columns can be moved or inserted ..... <<

In other words, the rows are "fixed" and the columns are not. Please
note that I put quotes around fixed. If you move the rows around, you
can easily modify four formulae via InsertNameDefine, right?

Thanks again. Appreciate your example.

Epinn

"Roger Govier" wrote in message
...
Hi Epinn

To give you a practical example of how I use Evaluate.
I am often involved in developing applications for clients where
financial transactions are involved. Here in the UK we have different
VAT rates Standard 17.5%, Lower 5% and Zero 0% (there are many others,
but these will suffice for the example)

On my Sheet I have column headings called VAT_Rate, Gross, Net and VAT
and when the user enters the VAT_code (Data validation used to limit to
one of the valid Vat codes) and Gross amount, I then want to calculate
the Net of VAT figure and the amount of VAT.
Rather than use a series of IF statements or lookup tables for making
the calculation, I make use of EVALUATE.

I set up the following named ranges / formulae

VAT_Rate = INDEX(Sheet3!2:2,MATCH("VAT_Rate",Sheet3!$1:$1,0))
Gross = INDEX(Sheet3!2:2,MATCH("Gross",Sheet3!$1:$1,0))
Net = INDEX(Sheet3!2:2,MATCH("Net",Sheet3!$1:$1,0))
VAT = INDEX(Sheet3!2:2,MATCH("VAT",Sheet3!$1:$1,0))
as you can see, these are all identical, and are just using Match to
find the relative position of each column within the table.

Now define the calculation that turns Gross to net for each of the Vat
rates
s =1/(1+17.5%)
l =1/(1+5%)
z =0%
and finally, the Evaluate to turn the relevant code for that row that
into a value
Vatcalc =EVALUATE(VAT_Rate)

In the column called Net I just use the formula
=Gross*Vatcalc
In the column called VAT I use
=Gross-Net

All of these formulae are relative to the row being used on the sheet,
columns can be moved or inserted and there is no use of the volatile
functions Offset or Indirect.

I have always thought it is a great shame that EVALUATE cannot be used
directly in a cell, but, with named formulae there is a way around the
problem.
--
Regards

Roger Govier


"Epinn" wrote in message
...
Biff,

Wow! You read my mind. I was happy with my test on a range but I felt
that it wasn't dynamic enough. With your formula, I don't have to hard
code the range when I define the formula. Great!

However, I am not familiar with R1C1 references; my only encounter with
it is via ToolsOptionsGeneral. I know "FALSE" in the formula
indicates that R1C1 style is used. Can you or someone point me to some
reference material on R1C1 please?

I tested RC[+5] for the fifth column to the right. I know INDIRECT is
not confined to the local sheet with A1-style. How about R1C1-style?
Can we refer to a different sheet? What are other codes than RC[ ]?

Thanks.

Epinn

"Biff" wrote in message
...
But I believe you need to use an
absolute reference in the defined name formula.

You can use R1C1 references:

=EVALUATE(SUBSTITUTE(INDIRECT("RC[-1]",FALSE),",","+"))

Refers to the column to the immediate left of the column where the
formula
is entered. For example, if the formula (=csum) was entered in B1 the
reference would be to A1.

Biff

"Ron Rosenfeld" wrote in message
...
On Sat, 14 Oct 2006 19:57:04 -0400, "Epinn"

wrote:

Does EVALUATE( ) still exist in V. 2003? I couldn't find it from
Excel
Help. Seeing that DATEDIF can't be found via F1 but still alive and
kicking, I need to hear about EVALUATE ( ) for sure although I think I
see
something like "undefined." If it is not available, what do we use
now?
I found it from the following writeup. By the way, does naming a
formula
goes thru the same steps as naming a range on a worksheet?
InsertNameDefine?

************************************************ **********************

Created by David Hager
To add comma delimited values in a cell (such as 1,2,11,4 in cell A1)
to
the right of the cell containing the string, highlight cell B1 (for
this
case) and create the following defined name formula (called "csum"):

=EVALUATE(SUBSTITUTE(A1,",","+"))

Then, type =csum in B1 to obtain the result (18, in this case).

************************************************ ***************

Thank you for your help.

Epinn



It is still present in Excel 2002.

BUT

EVALUATE is an old macro command from when Excel used to have Macro
sheets.

Entering and using it in the manner outlined by Hager still works in
Excel
2002
and I'd guess it'd work in Excel 2003. But I believe you need to use
an
absolute reference in the defined name formula.

You CANNOT use EVALUATE in a worksheet cell by itself (you never
could).


--ron