If only
Making decisons on what course of action to take is something we do almost every second of the day. It is one of the things that makes us intelligent and adaptable.
A simple example one might make of a Saturday afternoon:
"If it's sunny I'll go to the park. Otherwise I'll go to the pub."
Computers, wanting to imitate intelligence, also have a process for making decisions: the IF statement. Broken down into computer jargon, it looks like this:
"If expression is TRUE, then do statement1, else do statement2.
In our Saturday afternoon example
- expression = "It is sunny"
- statement1 = "go to the park"
- statement2 = "go to the pub".
The IF statment is implemented in Excel like this:
=IF(logical_test,[value_if_true],[value_if_false])
So with our example, we could write something like this:
=IF(Weather=Sunny,GoToPark,GoToPub)
Now, we probably won't be using Excel to decide what to do with our saturady afternoon, but we might like to use Excel to help us decide if a number is "High" or "Low". Lets assume we have a column of numbers in column C and we want to define them as being either high or low, with a high number being anything over 10. In cell D2 we enter the formula
=IF(C2>10,"High","Low")
First, the logical_test C2>10 is evaluated. Excel looks at what is in cell C2 (lets say that it is the number 6) and checks if this is greater than 10 or not. Since 6 is not greater than 10 the logical_test is FALSE, and the IF function jumps to value_if_false, which returns "Low". This word appears in cell D2. (By return I mean to say that the formula sends the value to the spreadsheet and you see it in the cell.)
If we [fill this formula down] the column, column D will now hold the values "High" and "Low", depending on the value in column C.
Perhaps we want to extend this a little so that a number above 10 is "High", a number between 10 and 4 is "Medium", and a number below 4 is "Low". This could be used as the basis of some trafic-light style display. We can do this by putting a second IF statement inside the first. This is known as 'nesting' funtions' and looks like this:
=IF(C1>10,"High",IF(C1<4,"low","medium"))
Instead of a simple output for value_if_false, we have put anoter IF statement there. So, if the first IF statement is FALSE, the second IF statement is evaluated. Let me write the formula above out in long hand so you can see what is going on more clearly:
If C1 is greater than 10, return "High". Else, if C1 is less than 4 then return "Low". Else return "Medium".
You don't need to explicitly test to see if the number is between 10 and 4, because by the time the functions gets to that part, you have already caught numbers higher than 10 and lower then 4, leaving only... numbers between 10 and 4!
We could now use this new column as the basis for [conditional formatting].
It is possible to nest 7 IF statements in Excel 2003 (64 in Excel 2007). However, past 3 or 4 nested statements, things start to get a little messy and complicated, and (depending on your needs) it may be easier to use a function such as the [VLOOKUP] function, or a [User Defined Function].
--- --- ---
[Note] Items that are surrounded by square brackets in this post are intended as future posts. When I get them up I will link to them.
