Using "IF" and "OR" together

macabre100

New Member
Joined
Mar 25, 2005
Messages
29
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!!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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

New Member
Joined
Mar 25, 2005
Messages
29
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,123
Messages
5,576,227
Members
412,709
Latest member
AD04
Top