Formula Trouble with Blank Cells

Occasional Contributor

I am trying to do something which seems simple, but having issues...


In column B I am trying to enter a value of 5 anytime Column A (the result of which is generated by a formula) has a number greater than 0 in it, otherwise leave it as blank.


I'm using the formula:  IF(A1>0,"5","")


This works fine if A has a 0 or a  # but sometimes A is blank.  When it is blank, i still get a value of 5 in column B.  If i change the formula for A to force the blank to be a 0, it still doesn't work (guess because it is not a true 0).


I can filter column A by blanks and then manually adjust column B for these cases but trying to find a formulaic solution.


Any thoughts?




3 Replies
best response confirmed by Hans Vogelaar (MVP)


If result in A1 is generated by formula the cell can't be blank. Most probably formula returns empty string or some number.

=IF( A1="", "", IF(A1 >0, 5,"") )
Thank-you! That worked and is exactly what I needed it to do!!!

@lhunter700 , you are welcome