Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    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,637
    Thanks
    115
    Thanked 650 Times in 592 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-29 at 00:20.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 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,280
    Thanks
    3
    Thanked 191 Times in 177 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
    2,826
    Thanks
    136
    Thanked 482 Times in 459 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 04:24. Reason: typo

  8. #8
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    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
  •