Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    395
    Thanks
    3
    Thanked 0 Times in 0 Posts

    If and statements

    I am trying to create a formula which looks at a number in a cell D6 and applies the following tests in cell T6

    the length > 10 digits
    the first four digits does not equal 9000,8000,7000
    if all four (4) tests pass then and the the bring in a number from cell E6 otherwise 0.

    my '=IF(AND(LEN($D6)>10,LEFT(($D6,4)*1<>9000),LEFT(($ D6,4)*1<>8000),LEFT(($D6,4)*1<>7000),$E6,0))

    Any one have any ideas where the BUG in the formula is?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,884
    Thanks
    147
    Thanked 734 Times in 666 Posts
    MNN

    Will there be decimals in any of the numbers? If so, your conditions Len and Left functions will count the decimal point in the length.

    Otherwise, try: =IF(AND(LEN($D6)>10,VALUE(LEFT($D6,4))<>9000,VALUE (LEFT($D6,4))<>8000,VALUE(LEFT($D6,4))<>7000),$E6, 0)

    HTH,
    Maud

    Can't explain why it looks like a space is inserted after Value but there should be no spaces
    Last edited by Maudibe; 2013-07-28 at 23:20.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,367
    Thanks
    164
    Thanked 625 Times in 593 Posts
    Hi MNN

    The bug in your formula is the wrong use of brackets.
    Your formula should be:
    =IF(AND(LEN($D6)>10,LEFT($D6,4)*1<>9000,LEFT($D6,4 )*1<>8000,LEFT($D6,4)*1<>7000),$E6,0)

    But please note that, as Maudibe says above, if the value in cell [D6] is numeric, the decimals will be counted in the LEN function even if they are not actually displayed in the cell format for cell [D6] e.g. 1234 may be displayed but the cell could have a value of 1234.56789012 etc etc.

    zeddy

    zeddy

  4. #4
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Hi

    Noting all that has been said by maudibe and zeddy, you formula can be simplified to

    =IF(AND(LEN($D6)=10,--LEFT($D6,4)<7000),$E6,0)
    Regards
    Roger Govier
    Microsoft Excel MVP

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,326
    Thanks
    3
    Thanked 215 Times in 198 Posts
    Or even:
    =IF(AND(LEN($D6)=10,--LEFT($D6,1)<7),$E6,0)
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Lounger
    Join Date
    Apr 2012
    Location
    Abergavenny, Wales, UK
    Posts
    25
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by rory View Post
    Or even:
    =IF(AND(LEN($D6)=10,--LEFT($D6,1)<7),$E6,0)
    Quite right Rory - why type all those extra zeros!!!!!!
    Regards
    Roger Govier
    Microsoft Excel MVP

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,367
    Thanks
    164
    Thanked 625 Times in 593 Posts
    Hi Roger and Rory

    You both seemed to have missed the requirement.

    if [D6] had an entry of 9123xxxxxxxxxx then,
    'the length > 10 digits
    the first four digits does not equal 9000,8000,7000'
    ..so show contents of cell [$E6]
    ..whereas, with your formula and this entry, it will incorrectly show 0

    zeddy
    Last edited by zeddy; 2013-07-30 at 03:24. Reason: typo

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    395
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thank you all

Posting Permissions

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