1. 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. 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

3. Hi MNN

The bug in your formula is the wrong use of brackets.
=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. 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)

5. Or even:
=IF(AND(LEN(\$D6)=10,--LEFT(\$D6,1)<7),\$E6,0)

6. Originally Posted by rory
Or even:
=IF(AND(LEN(\$D6)=10,--LEFT(\$D6,1)<7),\$E6,0)
Quite right Rory - why type all those extra zeros!!!!!!

7. 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

8. 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
•