Sunday, February 27, 2005

AND and OR Functions in Excel

Logic functions in Excel follow the same rules you learnt at school (or didn't learn as the case maybe.

AND requries all the arguments of the function to return a TRUE result in order to return TRUE. If any 1 argument returns FALSE then the entire funciton will return FALSE.

OR on the other hand requires any 1 argument to return TRUE and the result will be TRUE regardless of how many FALSE arguments there may be.

If all that was gobbledy-gook then let me use some examples to illustrate how people use these logic structures everday without even noticing.

Your car battery is charged = TRUE, AND your fuel tank is not empty = TRUE makes you can start your car = TRUE. If either of these arguments returns a FALSE result then you cannot start your car and the function result is FALSE.

You are a male OR you are a female = TRUE. Regardless of the result of either individual argument, as long as one of them is TRUE (and I hope its is for you dear reader!) then the result is TRUE.

So how do we use this in Excel?

Typically I will use the AND statement to return a result where I need all the individual components to be TRUE before getting a correct answer.
Thus in a spreadsheet containing 3 columns of data I can check that each column has specific data in it by adding a 4th column to validate it.
EG =AND(A1<>"",B1<>"",C1<>"")
This checks cells A1, B1 and C1 to see if they contain something (anything except an empty cell will return TRUE for each argument). If any of the cells is blank then my validation formula will return FALSE.
I use AND in my article about conditional formatting.

OR I use to test results. Eg testing a cell where gender is required to be entered as either M or F. Here a formula such as =OR(A1="M",A1="F") can be used. If either of these values are entered then the formula will return TRUE. If nothing or an alternative gender is entered than FALSE is returned.

This is obviously just brushing the field of logic. Far more complex calculations can be created and used. Contact me if you think you can benefit from learning more about this.