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
Johnette ConnelleyBudget Analyst / ProjectsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Lucas TrailCommented:
Assuming a1 can only be a number or blank this should work:  =if(a1="",b1,if(a1=0,b1,a1))
0
Rodney EndrigaData AnalystCommented:
Try this formula in cell C1:

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

Open in new window


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."))

Open in new window

0
Johnette ConnelleyBudget 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
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Rodney EndrigaData 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
Johnette ConnelleyBudget 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
Saqib Husain, SyedEngineerCommented:
=IFERROR(A1/A1,B1)
0
Rodney EndrigaData AnalystCommented:
No problem, here is the adjusted formula:

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

Open in new window


This will return the B1 value, regardless.
0
Johnette ConnelleyBudget 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
Saqib Husain, SyedEngineerCommented:
Sorry

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Johnette ConnelleyBudget 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.