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.

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

Hope this helps.
--
John C


"Nate" wrote:

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.

"John C" wrote:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

Hope this helps.
--
John C


"Nate" wrote:

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.

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?


--
John C


"Nate" wrote:

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.

"John C" wrote:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

Hope this helps.
--
John C


"Nate" wrote:

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.

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.

"John C" wrote:

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?


--
John C


"Nate" wrote:

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.

"John C" wrote:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

Hope this helps.
--
John C


"Nate" wrote:

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.

Sorry, misunderstood, here you go
=IF(OR(A1="",B1=""),"",A1-B1)

or

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

Hope this helps.
--
John C


"Nate" wrote:

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.

"John C" wrote:

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?


--
John C


"Nate" wrote:

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.

"John C" wrote:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

Hope this helps.
--
John C


"Nate" wrote:

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.

Thanks!

"John C" wrote:

Sorry, misunderstood, here you go
=IF(OR(A1="",B1=""),"",A1-B1)

or

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

Hope this helps.
--
John C


"Nate" wrote:

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.

"John C" wrote:

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?


--
John C


"Nate" wrote:

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.

"John C" wrote:

=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)

Hope this helps.
--
John C


"Nate" wrote:

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.