Value function

[Dave]

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.

 

[Jacob Skaria]

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

 

[Dave]

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]

The "/" in the text string do not represents a number.

If this post helps click Yes

 

[T. Valko]

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 Insert>Name>Define
Name: Calculate
Refers to: =EVALUATE(A1)
OK

Now type this into cell *B1* :

=CALCULATE

 

[Dave]

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.

 

[Roger Govier]

Hi Dave
You could get round it with a named formula
Insert>Name>Define>
Name Eval
Refers to =EVALUATE($A1)

In B1 enter =Eval and you will see the result 28.57143
Because the formula is locked to column A but relative for rows, as you copy
down it will give the corresponding results for A2, A3 etc.

 

[T. Valko]

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.