Why Value returns 1 (Excel 2003)

Topic

New Reply

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

Thanks Braddy

Reply | Quote

Replies

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)

Reply | Quote

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!

Many Thanks Braddy

Reply | Quote

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

Reply | Quote

Hi Hans

Thanks for the info

Braddy

Reply | Quote

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

Reply | Quote

Hi Aladin

Thank you very much for your input.

Braddy

Reply | Quote