IF, OR, ISERROR?

[FJ]

Hi, I’m trying to write a formula in column C. Basically, if the result of
A1-B1 is either a negative number or gives an error message, such as #VALUE,
I want cell C1 to show 0. I realize this is probably very simple, but I am
really stumped. I've tried many different combinations using IF, OR, and
ISERROR but so far I haven't had any success. Can anyone help?

Thank you in advance for any information.

 

[David Biddulph]

=IF(ISERROR(A1-B1),0,MAX(0,A1-B1)) assuming that you might want it to show
A1-B1 in the cases for which you haven't specified an output.
or
=IF(ISERROR(A1-B1),0,IF(A1-B1<0,0,"whatever answer you want in this case"))

 

[Mike H]

Hi,

You didn't get around to telling us what was to happen if neither of these
conditions is TRUE so I guess a1-b1

=IF(OR(ISERROR(A1-B1),A1-B1<0),0,A1-B1)

Mike

 

[FJ]

Hi, Mike, thank you so much for your quick reply. Your formula worked
great and yes, you were right that if neither condition were true I wanted
A1-B1.

Thanks again!

 

[Joe User]

=IF(OR(ISERROR(A1-B1),A1-B1<0),0,A1-B1)

Or:

=IF(ISERRROR(A1-B1),0,MAX(0,A1-B1))

I'm not very familiar with Excel 2007, but I think that construction lends
itself well to the use of IFERROR, to wit:

=IFERROR(MAX(0,A1-B1),0)


----- original message -----

 

[Joe User]

=IF(ISERRROR(A1-B1),0,MAX(0,A1-B1))

Oops! I didn't notice that David had already posted the same suggestion.


----- original message -----

 

[FJ]

Hi, Joe, sorry to respond so late. I haven't had a chance to check here in a
while. Thanks for your formulas. They worked great.

 

[FJ]

Hi, David, sorry to respond so late. I haven't had a chance to check here in
a while. Thanks for your formulas. They worked great.