# Excel Formula to Choose between two values ignoring Zero & Blank cells

I am looking for a formula to do the following:

If there is a number in cell A1, then return the value in A1.
If A1 contains a zero of the cell is blank, then return B1.  It sounds so easy so I'm think I'm making it too hard.

A1               B1               C1
50                                    Result
###### Who is Participating?

EngineerCommented:
Sorry

=IFERROR(A1*A1/A1,B1)
0

Commented:
Assuming a1 can only be a number or blank this should work:  =if(a1="",b1,if(a1=0,b1,a1))
0

Data AnalystCommented:
Try this formula in cell C1:

``````=if(or(A1="",a1=0),b1,a1)
``````

Copy this formula down to the necessary rows of data.

If you want to take it a step further, this formula will check the Cell to ensure it is an actual number:

``````=IF(OR(A1="",A1=0),B1,IF(ISNUMBER(A1),A1,"Not a valid number."))
``````
0

Budget Analyst / ProjectsAuthor Commented:
This will work IF someone doesn't clear cell a1 by pressing the spacebar.  Since I am writing this for others to use, I want to try and error proof it as much as possible as I know someone will clear the cell by pressing the spacebar instead of entering a zero or clearing it properly.  Any suggestions for adding a function to ignore that issue?  I think it would work perfect then.
0

Data AnalystCommented:
jconnelley44, did you try to apply my "updated" formula?

if someone happens to enter a value that is not a number, it will have an error message in the resulting cell.
0

Budget Analyst / ProjectsAuthor Commented:
The updated formula with the error message will work, just wish it would return the B1 cell value regardless of whether cell A1 has a zero, is blank because it was cleared properly OR is blank due to the spacebar being used to clear it.  Then, it would be perfect.
At least the error message will require them to go back and enter a zero in the field.
0

EngineerCommented:
=IFERROR(A1/A1,B1)
0

Data AnalystCommented:
No problem, here is the adjusted formula:

``````=IF(OR(A1="",A1=0),B1,IF(ISNUMBER(A1),A1,B1))
``````

This will return the B1 value, regardless.
0

Budget Analyst / ProjectsAuthor Commented:
=IF(OR(A2="",A2=0),B2,IF(ISNUMBER(A2),A2,IF(ISTEXT(A2),B2,)))
I think I figured it out by adding the "istext".  That seems to be working!
0

Budget Analyst / ProjectsAuthor Commented:
Saqib - That one works great!  The other will work, but this one is shorted and much more simple.
Thanks to all of you though!  I always learn SOO much from you all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.