# Thread: Why Value returns 1 (Excel 2003)

1. ## Why Value returns 1 (Excel 2003)

Hi
=IF(ISERROR(VALUE(B7)),B7,1 can anyone please tell me why this returns 1 whatever number is in B7, B7 actually contains =RIGHT(A7,1) but resulst in 6

2. ## Re: Why Value returns 1 (Excel 2003)

If B7 contains a value that looks like a number, VALUE(B7) will return that number.
Hence, ISERROR(VALUE(B7)) will be FALSE, for VALUE(B7) does not result in an error.
And so, the formula return the FALSE part of the IF function, i.e. 1.

If B7 contains an alphabetic character, VALUE(B7) will result in an error.
Hence, ISERROR(VALUE(B7)) will be TRUE.
The formula will return the TRUE part of the IF function, i.e. the character in B7.

Perhaps you need to swap the TRUE and FALSE parts?
=IF(ISERROR(VALUE(B7)),1,B7)
(I don't know what you're trying to accomplish)

3. ## Re: Why Value returns 1 (Excel 2003)

Hi Hans

This is an inherited Workbook and the owner is no longer with us, and I have been asked to make additions to the Workbook, but first I have to try and figure out what is happening.

I have never used Value before, I looked in help, and it said it returns a number from a text string, and because I did not have text string value I wasn't sure what was happening.

I have not yet discovered why he needed this result!

4. ## Re: Why Value returns 1 (Excel 2003)

The result of functions such as LEFT, MID and RIGHT is always a text string. It may look like a number, but it is text. In the screenshot below, A2 contains the number 678, and B2 contains the formula =LEFT(A2,1). Note that the value in B2 is left-aligned; this indicates that Excel "sees" it as a text value, even though it looks like a number. C2 contains the formula =SUM(A2:B2), and since SUM ignores text values, the result is 678, not 684.
(If you use =A2+B2, you force Excel to convert the value of B2 to numeric, so the result will be 684).

5. ## Re: Why Value returns 1 (Excel 2003)

Hi Hans

Thanks for the info

6. ## Re: Why Value returns 1 (Excel 2003)

If you use ISNUMBER instead of ISERROR, you'll have a more natural evaluation:

=IF(ISNUMBER(VALUE(B7)),B7+0,"Not Number")

or

=IF(ISNUMBER(B7+0),B7+0,"Not Number")