Formula woes...

[tlynn]

=IF(C12="","",IF(C12<>C10,IF(LEFT(C12,2)=47,47,"")))

I know that the "LEFT" function is text based, but it was a last ditch effort to get this to work.

Basically it starts by checking to see if c12 is filled or not...works fine. Then it goes on to see if c12 is different from c10 and if it is, is it filled with a 5 digit number starting with 47? If it is then the cell that this formula is nested in will, rather, should put a 47 in it and if there is not then the cell should remain blank or empty.

Any suggestions?

 

[jindon]

Hi

Have you tried

IF(C12="","",IF(C12<>C10,IF(And(Len(C12)=5,LEFT(C12,2)="47"),47,"")))

Left function returns string type result.

 

[debooo]

so if c10 said 47555 and c12 said 47111 the formula should return 47

but if either c10 and c12 is blank, or if either start with something other then 47, or if they are identical numbers the formula should reutn a blank?

 

[tlynn]

Jindon, no dice...if c10 and c12 are the same I get "FALSE"..if they are the same it should basically make the cell empty.

I think you are correct Debooo...

if c10 and c12 are the same the cell this formula is nested in should be blank. If c12 is different it should basically pop up a 47 in the cell.

 

[tlynn]

Maybe I am making this formula too difficult. If that is the case, any suggestion on a different way to do it would be appreciated...

 

[Yogi Anand]

=IF(C12="","",IF(C12<>C10,IF(LEFT(C12,2)=47,47,"")))

I know that the "LEFT" function is text based, but it was a last ditch effort to get this to work.

Basically it starts by checking to see if c12 is filled or not...works fine. Then it goes on to see if c12 is different from c10 and if it is, is it filled with a 5 digit number starting with 47? If it is then the cell that this formula is nested in will, rather, should put a 47 in it and if there is not then the cell should remain blank or empty.

Any suggestions?

Hi tlynn:

How about ...

=IF(C12="","",IF(C12<>C10,IF(LEFT(C12,2)="47",47,"")))

Does this work for you now?

 

[debooo]

=IF(C12="","",IF(C12<>C10,IF(AND(LEN(C12)=5,LEFT(C12,2)="47"),47," ")," "))

i barely tweeked the formula above, is this what you wanted?

 

[tlynn]

Again no dice...funny thing is that was one of the original ways I tried it. when C10 and c12 are the same I get a false statement...if both cells are the same the cell should remain blank. Any other suggestions?

 

[tlynn]

Debooo you are a rock star! Perfect! Thank you all for your help on this one.

 

[Yogi Anand]

Hi tlynn:

OK so how about ...

=IF(C12="","",IF(C12<>C10,IF(LEFT(C12,2)="47",47,""),""))