How do you know this stuff?

Through experience.

EVALUATE is an Excel macro function, it's not a regular worksheet function.
In fact, you can't use it directly on a worksheet and that's why you need to
create the defined name in order to use it.

Macro functions were used in older versions of Excel until they were
replaced with VBA, an extended version of the Visual Basic programming
language. The macro functions are still supported but not documented in
newer versions of Excel.


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Hi Bif,
Thanks for taking the time to explain that.
And putting 'Evaluate' into a named formula is completely new to me. I
couldn't find 'Evaluate' in the list of Worksheet functions, and the Help
gave no relevant hits. How do you know this stuff?

Dave.

"T. Valko" wrote:

No.

If A1 contains 200/7 that is a text string.

=Value(200/7)

Returns the correct result because 200 is being divided by 7 and that
result
which is a number is passed to the VALUE function for further
calculation.
However, using the VALUE function in this manner is redundant.

=VALUE(A1)

Retunrs a #VALUE! error because 200/7 is a rext string *and* the
calculation
of 200 divided by 7 is not taking place.

VALUE returns the numeric equivalent of a text number. To see how it
works
try this:

A1 = 200/7

Enter this formula in a cell, say D1:

=LEFT(A1,3)

The result is the TEXT string 200. To see that it is in fact a TEXT
string
try these formulas to test it:

=ISTEXT(D1) will return TRUE
=ISNUMBER(D1) will return FALSE

Also note that the cell alignment is to the left. TEXT aligns left and
numeric numbers align right.

Now try this formula in D1:

=VALUE(LEFT(A1,3))

Notice all the changes. The cell is now aligned right and the results of
ISTEXT and ISNUMBER have reversed.

Note however, the use of the VALUE function is almost never needed. You
can
convert TEXT numbers to NUMERIC numbers by using double negation. Like
this:

=--LEFT(A1,3)

Also, any math operation on a TEXT number will coerce it to a numeric
number:

=LEFT(A1,3)+0
=LEFT(A1,3)*1
=LEFT(A1,3)/RIGHT(A1,1)

To get the result that you expect requires a special functon. Try this:

*Select cell B1*
Goto the menu InsertNameDefine
Name: Calculate
Refers to: =EVALUATE(A1)
OK

Now type this into cell *B1* :

=CALCULATE


--
Biff
Microsoft Excel MVP


"Dave" wrote in message
...
Yeah, but the description of Value is:
Converts a text string that represents a number to a number.
So it shouldn't matter that xl identifies 200/7 as text.
Dave.

"Jacob Skaria" wrote:

This is similar to =VALUE("200/7"). Excel do not identify 200/7 as a
fraction
but as a text string; and hence returns error.

If this post helps click Yes
---------------
Jacob Skaria


"Dave" wrote:

Hi all,
XL2003.
I have a fraction, say 200/7, in A1.
In B1, I have: =Value(200/7), which returns the right answer.
In C1, I have: =Value(A1), which returns a #Value! error.
What am I doing wrong?
Thanks in advance.
Dave.