Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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


    Thanks Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    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!

    Many Thanks Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Why Value returns 1 (Excel 2003)

    Hi Hans

    Thanks for the info

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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")
    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Why Value returns 1 (Excel 2003)

    Hi Aladin

    Thank you very much for your input.

    Braddy
    If you are a fool at forty, you will always be a fool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •