What is evaluate formula?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rasoul Khoshravan - 24 Oct 2006 12:53 GMT

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

Reply to this Message

Kevin Vaughn - 24 Oct 2006 19:31 GMT

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.

Signature

Kevin Vaughn

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

Reply to this Message

romelsb - 24 Oct 2006 20:23 GMT

Thanks for the precautions....

Signature

"Bright minds are blessed to those who share them.."-rsb.

> Since no one else has replied yet, I'll take a shot. I created a named range
> called test1 as:
[quoted text clipped - 22 lines]
> > Is this a command name in Excel?
> > What is its function?

Reply to this Message

Rasoul Khoshravan - 25 Oct 2006 01:04 GMT

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

> Since no one else has replied yet, I'll take a shot. I created a named
> range
[quoted text clipped - 29 lines]
>> Is this a command name in Excel?
>> What is its function?

Reply to this Message

Epinn - 25 Oct 2006 19:05 GMT

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

> Since no one else has replied yet, I'll take a shot. I created a named
> range
[quoted text clipped - 28 lines]
>> Is this a command name in Excel?
>> What is its function?

Reply to this Message

Rasoul Khoshravan - 25 Oct 2006 18:18 GMT

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?

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

> Since no one else has replied yet, I'll take a shot. I created a named
> range
[quoted text clipped - 29 lines]
>> Is this a command name in Excel?
>> What is its function?

Reply to this Message

Epinn - 26 Oct 2006 10:57 GMT

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?

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

"Rasoul Khoshravan" <khoshravan@gmail.com> wrote in message
news:uifVCtD#GHA.5092@TK2MSFTNGP04.phx.gbl...
Thanks for your reply but I couldn't understand your explanation on
Evaluate.

> Since no one else has replied yet, I'll take a shot. I created a named
> range
[quoted text clipped - 28 lines]
>> Is this a command name in Excel?
>> What is its function?

Reply to this Message

Epinn - 24 Oct 2006 21:06 GMT

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?

Reply to this Message

Epinn - 25 Oct 2006 02:13 GMT

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?

Reply to this Message

Rasoul Khoshravan - 25 Oct 2006 01:04 GMT

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?

Reply to this Message

Lori - 26 Oct 2006 13:49 GMT

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

Reply to this Message

Lori - 27 Oct 2006 13:52 GMT

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.

Reply to this Message