Results 1 to 8 of 8
Thread: If and statements

20130728, 22:08 #1
 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?

20130728, 23:16 #2
 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 spacesLast edited by Maudibe; 20130728 at 23:20.

20130729, 02:56 #3
 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

20130729, 06:07 #4
 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

20130729, 07:50 #5
 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

20130729, 08:44 #6
 Join Date
 Apr 2012
 Location
 Abergavenny, Wales, UK
 Posts
 25
 Thanks
 0
 Thanked 3 Times in 3 Posts

20130729, 08:54 #7
 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
zeddyLast edited by zeddy; 20130730 at 03:24. Reason: typo

20130729, 20:11 #8
 Join Date
 Jun 2005
 Posts
 395
 Thanks
 3
 Thanked 0 Times in 0 Posts
Thank you all