# 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?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
EngineerCommented:
Sorry

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

Experts Exchange Solution brought to you by