Excel Worksheet Tips: Functions Part 1 – IF, OR & AND functions
I mentioned in a previous post about the IF function. I thought it would be appropriate to go into further detail about these common worksheet functions.
The IF function is a decision-making function which performs task A or B depending on the outcome of the decision. For example, if you were thinking of going for a walk, you would look outside and inspect the weather. If the sun was shining and it didn’t look like it was going to rain, then you would go for a walk. So, if this were encapsulated in the IF function, it would look something like this:
IF the sun is shining outside THEN
Go for a walk
As you can see, this is quite simple. To use the IF function in excel, the formula is as follows:
IF(condition,value if true, value if false)
The true and false values do not have to be integers, they can be other functions.
The OR function returns a positive value if one of the conditions is met. For example, you want to go out for dinner and if you have the money or your friend does, then you will go, otherwise you will stay home. If this were encapsulated using the OR function, it would look something like this:
IF (I have money OR my friend has money) THEN
Go out for dinner
To use the OR function in excel, the formula is as follows:
IF(OR(condition1, condition2, condition3),value if true, value if false)
The AND function tests if all conditions are true. For example, if you were trying to decide to take an umbrella with you to work, you would look outside and look to see if it looked overcast. You would also watch the weather report, and if looked overcast and the weather report said it was going to rain, you would take an umbrella. If the weather report said it was going to rain, but it looked okay out the window, then you wouldn’t take an umbrella. If this were encapsulated using the AND function, it would look something like this:
IF(the sky looks overcast AND the weather forecast said it would rain) THEN
Take an umbrella
Leave the umbrella at home
To use the AND function in excel, the formula is as follows:
IF(AND(condition1, condition2, condition3),value if true, value if false)
The AND and OR functions can be nested. For example:
IF( it is sunny AND (I have enough energy OR I had too many biscuits for a snack))THEN
Go for a run
Hopefully this has helped explain some of the basic functions available in excel.
Come check out the directory for the rest of my excel tips!