if " value in field name" is blank or under 100 return 0 otherwise return 1 - how do I get this formula to work

[Cathy2304]

I have a spreadsheet with dates in see below - column M is the difference between G and F - if G is Blank then it returns a blank in M - what I want is for the value in column u to show a 1 if the the value in column n is more than 100 - at the moment it also returns a 1 if the value is blank so the formula i came up with is:

=if(isblank(m2),0,(if(M2)<=100,1,0))

obviously doesn't work - any suggestions please)

t

 

[Joe4]

Welcome to the Board!

That means that cell M2 is probably not blank, but rather a single space or something like that (a space is NOT the same a blank).
If it is a single space, try this:

=if(or(isblank(m2),m2=" "),0,if(M2<=100,1,0))

Or we might be able to address it by fixing the formula you have in cell M2, if you would post that formula for us.

 

[Fluff]

How about

=if(and(m2<>"",m2>100),1,0)

 

[Cathy2304]

How about

=if(and(m2<>"",m2>100),1,0)

Thanks, this works perfectly - much appreciated

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Scott Huish]

M2 can't possibly be blank and greater than 100 at the same time, so that check is unnecessary:
=--(M2>100)

 

[Fluff]

But there is a formula in M2 which presumably returns "" which is greater than 100.

 

[Scott Huish]

OK, then:
=--(N(M2)>100)