IF, OR functions

Hi Pier,
Thank you for your question:
In answer I am going to paste a response using a training piece that I have, it covers a little more than just If and Or but I think that the whole is relevant to your question:

Some of Excel's Logical Functions:
1. AND
2. FALSE
3. IF
4. NOT
5. OR
6. TRUE
The most commonly used Logical function is the IF Function. The IF Function, like all the logical functions, will return TRUE or FALSE.
Let's look at a simple use of the IF function. In this example we will use it to determine whether the cell A1 is greater than 100. If it is, then we will multiply that number by 10, otherwise if it's not greater than 100 we will divide 10 by it.
=IF(A1>100,A1*10,10/A1)
As you can see, the Syntax for the IF function is:
=if(logical_test,value_if_true,value_if_false)
Let us now suppose we need to check if cell A1 is between the value 100 and 200. For this we need to Nest the AND function into our logical_test for the IF function.
=IF(AND(A1>100,A1<200),A1*10,10/A1)
The AND has a Syntax of:
=AND(logical1,logical2,...) up to 30 logical
It will return True ONLY if all supplied logical arguments evaluate to True. If only one from many, evaluate to False and the rest are True, the AND Function will return False. As you can see we have told the AND function to check whether cell A1 is between 100 and 200 by telling it to evaluate the 2 logical arguments we have supplied. The result (TRUE or FALSE) is returned to the logical_test argument of the IF Function.
Let's now suppose we want to check the contents of 2 other cells and if either one is greater than 100 multiply A1 by 10.
=IF(OR(A2>100,A3>100),A1*10,10/A1)
The OR has a Syntax of
=OR(logical1,logical2,...) up to 30 logical
Very much the same as the AND function. The one and only difference is, only one of all supplied logical arguments needs to be true for the OR Function to return TRUE. In our example this means that if either A2 or A3 is greater than 100, the result TRUE is returned to the logical_test argument of the IF Function.
So far we have been making one VERY big assumption and that is, Cell A1 will house a number. Try typing some text in cell A1 and all our Function will return the #VALUE! error. This is simply because we cannot multiply or divide with text. To account for these types of situations we need to either first check A1 houses a number.
=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,10/A1),"")
The above is one way to do this and if cell A1 does not house a number then return "" (empty text). There is still a potential problem when A1 is equal to zero. We end up with the #DIV/0! error. Meaning we are trying to divide by zero and that's not possible. To account for this also, we could use
=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(A1>0,10/A1,"")),"")
This will now prevent trying to divide 10 by zero. If we wanted we could take, what I call the blanket approach, by using one of Excel's Information Functions. In this case the ISERROR or ISERR Functions. These have a Syntax of.
=ISERROR(value)
and
=ISERR(value)
They are both very similar but while the ISERROR checks for all errors (and returns TRUE or FALSE), the ISERR checks for all errors except the #N/A! and again returns True or False. Meaning we could use:
=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(ISERR(10/A1),"",A1/10)),"")
This will prevent any errors in regards to trying to divide by zero. The problem with this approach is you could well be masking an error that you perhaps should be aware of! One way to overcome this potential problem is to use the ERROR.TYPE Function. It has the Syntax
=ERROR.TYPE(error_val)
and again, will evaluate to either True or False. The error_val's are:
#NULL! = 1
#DIV/0! = 2
#VALUE! = 3
#REF! = 4
#NAME? = 5
#NUM! = 6
#N/A = 7
Anything else#N/A
Meaning, we could use:
=IF(ISNUMBER(A1),IF(OR(A2>100,A3>100),A1*10,IF(ERROR.TYPE(10/A1)=2,"",A1/10)),"")
This will only prevent the #DIV/0! error should A1 be zero.
Error checking/trapping in Worksheet functions can get quite complex very quickly. Even with our above formula we could still encounter an error within the OR Function. If this happens our OR function will return neither TRUE or FALSE, it will return an error and that will force our formula to again return an error. So as you can see, a simple formula can soon turn into a monster if we are to try and account for all possible errors. Truth is, it is not feasible to always to try and trap all errors. The best way to prevent errors is to prevent them at the source. In this case, the source is cells A1, A2 and A3 and the easiest way to ensure no invalid data goes into to these cells is by using the Validation feature found under Data on the main menu.

I hope that helps .... Pete