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!!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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,022
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,620
Messages
5,832,734
Members
430,160
Latest member
a_majda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top