Using "IF" and "OR" together

[macabre100]

I am trying to eliminate zeroes & "#n/a" or any other kind of errors from a list and replace it with a number.

I'm using the formula "=if(or(ISERROR(A1),A1=0),170,A1)". However, this formula only eliminates the zeroes, but keeps other errors. Am I missing something here?

Thanks in advance!!

 

[barry houdini]

Try

=IF(ISERROR(A1),170,IF(A1=0,170,A1))

 

[Greg Truby]

You can't join those expressions using an OR because if A1 does equal #N/A then the "A1=0" part of the OR gets tested and returns #N/A You could nest the IF()'s like so<ul>[*]=IF(OR(ISERROR(A1)),170,IF(A1=0,170,A1))[/list]

{edit}Improved formula to not error on text, but too late since Barry already beat me to it anyway!{/edit}

 

[macabre100]

Thanks, Barry & Greg!! Greg, your explanation helped me understand why mine wasn't working, so your effort didn't go waste!. Help much appreciated.

 

[Greg Truby]

When you run into situations like this, you can use the menu option Tools | Formula Auditing | Evaluate Formula to open a dialog box that will walk you through the formula expression by expression and you can see where your formula derails. Very helpful in debugging formulae.