Results 1 to 12 of 12
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Query (Excel 2003)

    I have set up the following formula to identify the 1st 2 values of a cell (which is in the format [2 characters (numeric)][3 characters (alpha/numeric)] and then I compare the numeric characters against 2 values to classify the codes.

    =IF(ISBLANK(E808),"",IF(LEFT(E807,2)<Groups!$E$2," DNG","DAM"))

    The problem I have is the result LEFT(E807,2) returned a text value and I am comparing this against a numeric value so I cannot get the final value correctly.

    Can anyone advise how I can convert this result to a numeric value so the comparison against the 2 cells will return the correct classification I am looking for.

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

    Re: Formula Query (Excel 2003)

    Try N(LEFT(E807,2))
    The N function converts any value to a number, if possible.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Query (Excel 2003)

    Thanks Hans

    I have tried this but no change.

    When I use this formula by itself looking at a cell which contains 91LR1 it returns no value. If I delete the n() part of the formula I get the 91 as the value.

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

    Re: Formula Query (Excel 2003)

    Try the following trick then: replace LEFT(E807,2) by

    (0+LEFT(E807,2))

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Query (Excel 2003)

    Hans, Thankyou that worked.

    Your effort and assistance is really appreciated.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Query (Excel 2003)

    I can't find a N() function in XL2K. It does seem to exist since I can us it in a formula, but if I put '35 in A1 and =N(A1) in another cell, it returns 0. I can't find N() in the help files. Did you maybe mean =VALUE()?
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula Query (Excel 2003)

    =N() has been around a while, see if it's under Information Functions. =VALUE() will atttempt to convert a text string to a number, where N will not convert text strings, returning 0 instead:

    in =N(value)
    If value is or refers to a number =N(value) returns that number
    If value is or refers to a date in a built-in date format =N(value) returns the serial number of that date
    If value is or refers to TRUE =N(value) returns 1
    If value is or refers to FALSE =N(value) returns 0
    If value is or refers to an error value, such as #DIV/0!, =N(value) returns the error value
    If value is or refers to anything else =N(value) returns 0
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Query (Excel 2003)

    If that is what N() does, then it is definitely not the function that Hans meant to use in this response. VALUE() does what he wanted to do.
    Legare Coleman

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

    Re: Formula Query (Excel 2003)

    You are correct, Legare, I should have used VALUE instead of N. Thanks to you and unkamunka for pointing it out and doing the research.

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula Query (Excel 2003)

    That unkumunka, he's a great guy, isn't he? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula Query (Excel 2003)

    Both the N() and T() functions are pretty much redundant as far as I know, but are included for compatibility with older spreadsheet programs.

    They would most likely have a use if we did not have ISTEXT() and ISNUMBER().


    Andrew

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

    Re: Formula Query (Excel 2003)

    Oops, I'm very sorry, John. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thanks!

Posting Permissions

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