Adding LEN as the 3rd condition in an IF statement

[Nameless_]

Hi All,

I'm trying to add LEN in as a 3rd condition to a formula

This is what I think should work but excel doesn't like it

IF((and(b20=0,B22=16,len(b22)=1),"z0"&b22+1,"Z"&b22+1))

I want the output for the cell i'm putting the formula in to show Z0 and then cell B22+1, if the length of B22 is greater than or equal to 2 AND B20 contains 0

B22 will be 1, 2 or 3 digits long

 

[hagia_sofia]

There seems to be some confusion regarding the conditions - could you please answer the following: a) IF B22 = 16, then LEN of B22 can never be equal to 1, so the AND will always be FALSE b) "B22 is greater than or equal to 2" - does that mean its LEN or a number? c) "B20 contains 0" - at the moment your condition is set that B20 = 0 - should it be "equal to 0" or "contains 0"?

 

[Nameless_]

Thanks for the quick response.
Looks like I had a few typos in the formula

I got it working by using this formula:
=IF(AND(B20=0,B22<16,LEN(B22)=1),"Z0"&(B22+1),"Z"&(B22+1))

 

[Scott Huish]

Why don't you just check for B22 being less than 10 then you don't need LEN?