subject: Microsoft Excel Functions MasterClass - the Conditional If Function Explained [print this page] Microsoft Excel Functions MasterClass - the Conditional If Function Explained
The IF function in Excel takes the following format:
=IF(Whatever the condition is, What to do if it's true, What to do if it's false)
Here's an example. Supposing that you have:
The name of each person in your company in column A
The number of coffees they drink each day in column B
You want to pick out the caffeine addicts by displaying the word CAFFEINE in columnC if they drink more than 5 copies of coffee a day; otherwise you'll leave this cell blank.
The first thing to note about this problem is that - like so often in Excel - the best answer isn't actually to use an IF function. In this case you could just apply conditional formatting to column B to make the coffee-heads appear in a different colour. Howerver ...
To solve this problem, we want to enter the following formula in column C:
=IF(Number of coffees drunk > 5 THEN display "Caffeine" ELSE display nothing
Unfortunately that's not Excel syntax, so we just need to rewrite this in a format that Excel will understand to get:
= IF ( B1>5 , "Caffeine" , "" )
Note that I've put the spaces in to make this easier to read, but if you type this into Excel you'll need to remove them all.
You should now have the word Caffeine displayed in cell C1 if the person in row 1 drank more than 5 cups of coffee; otherwise, the cell will appear empty. Allthat you now need to do is to copy this formula down - the easiest way to do this is:
Click on the cell containing the formula (the one you've just entered).
Position the mouse pointer at the bottom right corner (a black cross will appear).
Double-click on this to copy the formula down to the cells below.
Each cell in column C should now contain the right word.
One final thought- what would happen if you wanted to grade the coffee-drinkers more subtly? Suppose anyone who drinks up to 4 coffees a day is "OK", 5-8 marks you as a "Problem drinker" and 9+ implies that you're an addict.
One answer to this problem is to nest one IF function within another:
