What is evaluate formula?

K

Kevin Vaughn

Since no one else has replied yet, I'll take a shot. I created a named range
called test1 as:
=EVALUATE(Sheet3!D5+Sheet3!E5)
Both d5 and e5 had numbers, and when I put =test1 into a cell, it gave me
the sum of those numbers.

I have a feeling, but am not positive, that evaluate was part of the prior
macro language that Excel used; I believe it was called XLM.

BTW, as the cells reference are relative, if I use the =test1 formula
elsewhere, it should give me the answer using the cells relative to where my
cursor was prior to creating the named range (I believe I was in D6 when I
created the range name.) Let me test this to make sure by putting the
formula in a different cell and observing the results:
So, it turns out that I was in E6 when I created the named range and yes, it
does add the cell above and the cell above and to the left wherever you enter
the formula =test1

If it is indeed from XLM, then there is a caveat to watch out for (and I
never remember the exact details. Something like don't copy the name or the
spreadsheet to another workbook or, at least in Excel 2000, it could cause a
crash.) I never have and never will experiment with this to see if it does
happen as I never deliberately try to crash my system.
 
E

Epinn

We have recently had an interesting and detailed discussion on EVALUATE. Please feel free to do a search - same forum, October 14.

Epinn

Is this a command name in Excel?
What is its function?
 
R

Rasoul Khoshravan

Thanks. That was interesting.
Are you asking something totally different from EVALUATE ( )?

I use the term "Evaluate Formula" quite often in my posts. It is an
excellent tool. Try it and you may like it. Click on a cell that contains
a formula and then click Tools>Formula Auditing>Evaluate Formula.

Epinn

Is this a command name in Excel?
What is its function?
 
R

Rasoul Khoshravan

Thanks for your reply but I couldn't understand your explanation on
Evaluate.
 
E

Epinn

Are you asking something totally different from EVALUATE ( )?

I use the term "Evaluate Formula" quite often in my posts. It is an excellent tool. Try it and you may like it. Click on a cell that contains a formula and then click Tools>Formula Auditing>Evaluate Formula.

Epinn

Is this a command name in Excel?
What is its function?
 
R

Rasoul Khoshravan

thanks. I read it but need more time to think and understand.
If I understand correctly, it stores a series of functions under name and
uses EVALUATE funtion to recall it. Is this correct?


The thread on Oct. 14 that I mentioned in my previous post explains EVALUATE
( ) in great detail. A real life example was given. It would be worth
reading. Did you have a chance to take a look?

Epinn

Thanks for your reply but I couldn't understand your explanation on
Evaluate.
 
E

Epinn

I couldn't understand your explanation on Evaluate.

The thread on Oct. 14 that I mentioned in my previous post explains EVALUATE ( ) in great detail. A real life example was given. It would be worth reading. Did you have a chance to take a look?

Epinn

Thanks for your reply but I couldn't understand your explanation on
Evaluate.
 
E

Epinn

As Roger suggested in the thread, prepare the worksheet i.e. define the formulae (Insert>Name>Define), key in the column headings, enter the formulae to the cells etc. etc. If you like you can even use Tools>Formula Auditing>Evaluate Formula to see the steps of the formula unfold. I think doing it is more effective than reading and thinking. Just a thought.

By the way, when you first posted, did you have EVALUATE ( ) or Evaluate Formula tool in mind? I am curious.

Epinn

thanks. I read it but need more time to think and understand.
If I understand correctly, it stores a series of functions under name and
uses EVALUATE funtion to recall it. Is this correct?


The thread on Oct. 14 that I mentioned in my previous post explains EVALUATE
( ) in great detail. A real life example was given. It would be worth
reading. Did you have a chance to take a look?

Epinn

Thanks for your reply but I couldn't understand your explanation on
Evaluate.
 
L

Lori

I've found this form useful: it's possible to pass an argument to the
evaluate function.
With cell A1 selected define the name eval as below:

eval: =EVALUATE(EVALUATE("if("&MID(GET.CELL(6,!A1),6,250)))

Then you can enter formulas in any cell in the form:

=IF(1,eval,TextToEvaluate)

eg:

=IF(1,eval,"1+2*3") returns 7

or if A2 contains the text 1,2,3,4,5

=IF(1,eval,SUBSTITUTE(A2,",","+") returns 15
 
L

Lori

The above method can also handle calculations that are difficult or
time consuming using conventional worksheet functions such as text
calculations or calculations across worksheets. e.g.

A1: Sheet5

A2: =IF(1,eval,"sum(sheet1:"&A1&"!A:A")

sums all numbers in first column of sheets 1 to 5. Or:

A1: 6 crates of 50 apples @ $ 0.40 per apple

A2: =IF(1,PRODUCT(IF(ISNUMBER(--eval),--eval)),"{"""&SUBSTITUTE(A1,"
",""",""")&"""}")

as an array formula returns the correct value $120.
 
Top