If and statements

Topic

New Reply

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)*19000),LEFT(($D6,4)*18000),LEFT(($D6,4)*17000),$E6,0))

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

Reply | Quote

Replies

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

Reply | Quote

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)*19000,LEFT($D6,4)*18000,LEFT($D6,4)*17000),$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

Reply | Quote

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)

Reply | Quote

Or even:
=IF(AND(LEN($D6)=10,–LEFT($D6,1)<7),$E6,0)

Reply | Quote

Or even:
=IF(AND(LEN($D6)=10,–LEFT($D6,1)<7),$E6,0)

Quite right Rory – why type all those extra zeros!!!!!!

Reply | Quote

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

Reply | Quote

Thank you all

Reply | Quote