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?
 
Saqib Husain, SyedEngineerCommented:
Sorry

=IFERROR(A1*A1/A1,B1)
0
 
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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

All Courses

From novice to tech pro — start learning today.