Subtracting with cells that appear blank

  • Thread starter Thread starter Nate
  • Start date Start date
N

Nate

I am trying to create a formula that subtracts one cell from another, such as
A1 - B1. The problem is that some of the cells in columns A and B have
returned a blank value and our causing a #VALUE error to occur when I try to
perform this simple operation. I get the #VALUE error if there is a blank
cell in either column A or B, or if both columns are blank. I would like it
to return a blank instead of the #VALUE error. I have been trying to do an
if formula, but am having a hard time getting all 3 situations accounted for.
Any help would be greatly appreciated.
 
Unfortunately the cells aren't actually blank, so the ISBLANK formula does
not work. Also, I don't want it to return a blank value if only one of the
cells in either column appears blank. Thanks for the suggestion.
 
Well, that is what you asked
....The problem is that some of the cells in columns A and B have
returned a blank value and our causing a #VALUE error to occur when I try to
perform this simple operation. I get the #VALUE error if there is a blank
cell in either column A or B, or if both columns are blank. I would like it
to return a blank instead of the #VALUE error...

When you say some cells in A & B return a blank value, what did you mean?
When you say there is a blank cell in either A or B, or if both are blank,
what did you mean?
When you say, you would like it to return a blank instead, what did you
really want?
 
The cells appear blank, because my IF(ISNA(VLOOKUP(),"", formula didn't find
a match for the Lookup Value. Any time you want a cell to appear blank
instead of returning a value you can use a "". The problem is that although
the cell appears blank, it doesn't behave the same way as a normal blank cell
would. Thanks anyways.
 
Sorry, misunderstood, here you go
=IF(OR(A1="",B1=""),"",A1-B1)

or

=IF(AND(ISNUMBER(A1),ISNUMBER(B1)),A1-B1,"")

Hope this helps.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Concatenate with line breaks but ignore blank cells 3
Excel formula, sum the value cell and if empty cell left blank 0
Help needed 0
Need Formula - If blank cells 3
Having Blank Cells, etc. ? 1
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria) 1
Automatically Enter Formula in a Cell After Entering Data in Another Cell 0
Blank Suppression 3

Back
Top