ignore if cell is blank

[Barny72]

So I am using this formula to return a value of 1 if true
=IF(a1<b1,1,)
this works but i need it to ignore when a cell is blank - I can't seem to get it right in that respect.

can you help please

 

[Trebor76]

Hi Barny72,

You don't say what cell you want to check so this will return zero if either A1 or B1 is blank:

=IF(OR(LEN(A1)=0,LEN(B1)=0),0,IF(A1<B1,1,0))

Hope that helps.

Regards,

Robert

 

[Barny72]

yeah sorry...so the cell I want to check is b1. A1 will always contain a number but for B1 this is either populated manually with a number or left blank.

hope that helps

 

[Trebor76]

Try this:

=IF(LEN(B1)=0,0,IF(A1<B1,1,0))

 

[Cubist]

populated manually with a number or left blank.

What do you want to return if B1 is blank?

 

[Cubist]

Edited: Try this

Book2
	A	B	C
1	1	2	1
2	3	2	0
3	-2		0
4	-5	5	1
5	8		0
Sheet1

Cell Formulas
Range	Formula
C1:C5	C1	=(A1<B1)*(B1<>"")