Setting Conditions

You use conditions to build smart formulas--ones that look at your data and make their own decisions about what to do with it. In the exercises for this chapter, you'll learn to write several different types of conditional formulas.

Exercise #1: The school of Excel

In this exercise you use conditional formulas to give students a chance at a better mark, and to properly assign letter grades.

Begin by opening the The school of Excel.xlsx spreadsheet. You'll see a list of students and their marks.
Take a moment to familiarize yourself with the worksheet. Every student has a score for two tests and a final exam. Each score is shown both as the number of marks and a percentage. For example, Test #1 was out of 45 marks. Lisa Denchelgo scored 43 out of 45 (see cell C5), which works out to 96% (as calculated in cell D5). Do you see the total number of marks that were available for Test #2 and the final exam?

The total marks are in cells E4 and G4. If you don't spot them right away, you can check the percentage calculations in cells F5 and H5, which use these numbers.
Using this information, the worksheet calculates a combined score for each student. Can you figure out how this calculation is weighted--in other words, how the three individual scores are combined to get a final score?

The formula that does the work is in column I. Here's an example:

=H5*0.5+F5*0.25+D5*0.25

This formula takes the final exam score, weighted by 0.5 (50%), and adds the two test marks, weighting each one by 0.25 (25%). This calculation works as long as all the weights add up to 1 (100%). In this particular case, it means the final exam is twice as important as either test, because it accounts for half the mark on its own.
Now that you're well acquainted with what's going on in the worksheet, you're ready to enhance it. In this class, the instructor has offered students a special deal. If they score well on the gruelling final exam, they can use the exam as their whole final mark. There are a few different ways to achieve this result, but in this case you'll use a condition and the IF function. Can you come up with a condition that compares these two different score calculations? (You don't need to use the IF function yet. Just try to figure out the condition.)

It's easier than it may seem. The two values you need to compare are already on your worksheet. Cell I5 has the weighted score calculation, and cell H5 has the final exam mark. So the easiest way to write the condition is like this:

H5>I5

You might have reversed the order of the I5 and H5. Or, you might have used the < sign instead of the > sign. All of these variations are also correct. (It just means the order of the true and false parameters may change when you use the IF function.)
Now you can write a full formula that uses this condition with the IF function. Remember, your goal is to use whichever score is higher. Start by writing the formula for Lisa Denchelgo in cell J5. The result should be 82% (Lisa's combined score), because that's higher than her exam score (75%).

If H5>I5 is true, you should use H5. If not, you should use I5. Here's the formula that does it:

=IF(H5>I5, H5, I5)

You could write this formula many different ways. Here's one other example that's equivalent:

=IF(H5<I5, I5, H5)
Now copy your formula from cell J5 (move there and press Ctrl+C) and paste it to all the cells underneath (select them and press Ctrl+V). Which students benefit from the adjusted score?

Quite a few, including Anne, Marie, Karyn, Morgan, Elgin, and Ron.
The rules just became more strict. Now the special adjusted score offer will only be made available to students who have submitted all their labwork (students who have a Y in column B. What condition could you use to test if Lisa meets this requirement?

You need to perform a text comparison in this condition. It's easy, as long as you remember the quotation marks.

B5="Y"

This is true if cell B5 has Y or y in it, which is perfectly fine.
To put this condition into action you need to combine two conditions: the condition you've already written (which checks if the final exam score is higher than the combined score), and the new condition that checks the labwork. What function can you use to require two conditions to be true?

The AND function can combine two conditions.
Now you can edit the formula in J5. Keep the IF function, but replace the condition with an AND function that has two conditions.

The edited formula for Lisa should look like this:

=IF(AND(B5="Y", H5<I5), H5, I5)

There are a few different ways to write this formula. For example, it doesn't matter if you switch the order of the two conditions that the AND function uses.
Now copy the edited formula (Ctrl+C) and paste it for all the students (Ctrl+V). Can you tell who can't take advantage of a high final exam mark because of missing labwork?

Anne, Elgin, and Ron lose out.
Your final task is to take the adjusted score and assign a letter grade. The threshold for each grade is in cells M5 to M8. For example, if a student scores higher than the value in M5 (80%), they receive the letter grade in N5 (A). If they don't satisfy that requirement but they score higher than M6 (70%), they receive the grade in M7 (B), and so on. Can you accomplish this sort of logic with a single IF function? If not, what technique do you need to use?

A single IF function isn't enough. You need to use nested IFs.

Another way to think about it is each letter (A, B, C, D, and E) is a different outcome. A single IF function can choose between two outcomes. To get more, you need to put one IF inside another.
The easiest way to write a formula with nested IFs is to start by writing just the first condition. In cell K5, write a formula that checks if she has a score that's high enough for an A. If she does, display an A in the cell. If she doesn't, don't do anything else--yet.

You could write the formula like this:

=IF(J5>=80%, "A", "")

But a better approach is to use cell references to get the mark threshold (80%) and the letter grade (A):

=IF(J5>=$M$5, $N$5, "")

This way, your formulas become more flexible. In the future, you can change the mark thresholds or change how the letter grade is written, and your formulas will use the new information. You should also notice that the formula uses fixed references. That's because every student uses the same list of marks and letters. When you copy your formula, you don't want Excel to change these references, or your conditions won't work anymore.

In both versions of this formula, notice that you need >= rather than just > because you should get an A if your score is exactly 80%.
This takes care of students who get As, but what about lower marks? Right now, the formula just uses empty quotation marks to display a blank value (so nothing appears in the cell). But what you should do is use a second IF function. In other words, if the first condition is false (the student didn't reach 80%), use IF to check if the student met the next threshold (over 70%). Take a moment to add this second test to the formula.

Here are two different ways to write the revised formula. Here it is with the thresholds and letter grades typed right into the formula:

=IF(J5>=80%, "A", IF(J5>=70%, "B", ""))

And here it is with cell references that do the same thing:

=IF(J5>=$M$5, $N$5, IF(J5>=$M$6, $N$6, ""))

To test this formula, copy it to cell K6. You should see Anne's B grade appear.
Right now, your formula properly assigns A and B grades. (And shows a blank cell for everything else.) To properly finish this example you need to nest another IF for Cs, and another IF for Ds. If none of those conditions are met, the grade is obviously, and sadly, an E. Try writing the complete formula you need and try it out. You may find that it's easy to get the order wrong or leave out a bracket when you have so many layers inside one formula!

Here's the mammoth final formula, with the numbers written in:

=IF(J5>=80%, "A", IF(J5>=70%, "B", IF(J5>=60%, "C", IF(J5>=50%, "D", "E"))))

And here it is with cell references that do the same thing:

=IF(J5>=$M$5, $N$5, IF(J5>=$M$6, $N$6, IF(J5>=$M$7, $N$7, IF(J5>=$M$8, $N$8, $N$9))))

This version of the formula is for Lisa's row, but you can copy it to the entire worksheet to make sure it works. You should find one C grade, one D, and plenty of As and Bs.

If you have trouble getting the kinks ironed out of your formula, check the Solution worksheet to see the correct final product.

Exercise #2: The school of Excel (part 2)

It's time to head back to school. Now you'll use the conditional counting and summing functions to get some interesting overall information about your class.

Begin by opening the The school of Excel 2.xlsx spreadsheet. This is basically the finished worksheet from the end of the first exercise.
In this exercise you'll use some conditional functions that work with ranges. You'll write your formulas in a small group of cells: P12 to P16. Start by making sure you're in cell P12.
You need to write a formula that counts the number of students that have passed the course. What conditional function can do this job?

COUNTIF is perfect for the job.
To use COUNTIF, you need to pick a range of cells to examine, and the criteria you want to use to test them. Excel counts only the cells that pass the test. What range should you use?

You need to pick the same range that you'll be evaluating with your condition. This is the column with the final marks in it, cells J5 to J18.
In this example, a student is considered to have passed if they scored over 50% (although you could make a more stringent requirement, as some schools do). How would you write the condition the COUNTIF uses?

When you use COUNTIF, you supply a piece of text that holds part of a condition (starting with the logical operator you want to use). The COUNTIF function than creates a condition for each cell usign the cell reference and this piece of text. To require a mark to be above 50% with COUNTIF, you would use the text ">50%".
Now you can put these details together and write your formula that uses COUNTIF. If you do it right, you should get a count of 14 students, which indicates that everyone managed to pass.

The finished formula looks like this:

=COUNTIF(J5:J18, ">50%")
The next formula goes in cell P13. You need to calculate the average mark of all students who earned an A. Once again, you need a conditional formula, but this time it's AVERAGEIF. You could examine either the scores in column J or the letter grades in column K, but in this example let's use the numerical scores to be consistent with the previous formula. How would you write the AVERAGEIF formula?

=AVERAGEIF(J5:J18, ">=80%")

Don't forget to use >= rather than > in your condition because you want to include students who earned exactly 80%.
Time to write the formula in P14, which is a little bit more complicated. Now you need to count B and C students. You could use the COUNTIF function twice (one to count Bs and one to count Cs) and add the totals together, but we want to do it all with one function. What counting function can impose more than one condition that cells must satisfy in order to be counted?

The COUNTIFS function can apply any number of conditions.
You need to use two conditions to make sure scores fall between 60% and 80%. You need to make sure that the marks you count are 60% or above, and that they are under 80%. Only C and B marks fall in this range. Using this information, write the COUNTIFS function you need.

The formula should look like this:

=COUNTIFS(J5:J18, ">=60%", J5:J18, "<80%")

Notice that the range is specified twice, and it's the same both times. That's because both conditions are working with the same information: student scores.
Now move to cell P15, where you'll write another version of the COUNTIFS function. This time the twist is that you're evaluating two separate ranges of data. You want to check that scores are at least 80%, and you want to make sure the "Labwork submitted?" has an "N" for not submitted. If you get it right, your formula will product a result of 1, indicating that a single A student failed to turn in the labwork. How would you write this formula?

=COUNTIFS(J5:J18, ">=80%", B5:B18, "=N")

Notice that you don't need to put quotations marks around the N, like you usually would when writing a condition that compares text. That's because COUNTIFS is smart enough to understand what you're trying to do.
The final formula goes in cell P16. It uses the MINIFS function to find the lowest score that meets the criteria you specify. When using MINIFS, you provide two ranges. The first range has the cells that MINIFS will pick from. That's the student scores J5:J18. The second range has the cells that MINIFS will use to evaluate your condition. Which ones are these?

In this case, you want to consider the scores of students who didn't submit labwork. That means you need to consider cells B5:B18, which indicate which students have done their work.
Now that you know what ranges you're using, write the full MINIFS formula, including the condition. If you've done it correctly, you'll find that the lowest score of a student who didn't submit the homework is 57%.

Here's the formula:
=MINIFS(J5:J18, B5:B18, "=N")
Rewrite this formula to look for the lowest score from a student who did submit homework, and the result will change to 63%.
For a bonus challenge, calculate the average score of students who didn't do labwork and compare it to the average score of students who did. You'll use the familiar AVERAGEIF function to do your calculations. But there's one trick. You need to give AVERAGEIF three parameters: a range, a condition, and another range. The first range is the range you're using to evaluate your condition (the labwork column). The second range is the range you're using to perform your average calculation (the column with the scores).

Check the Bonus worksheet to see these two extra AVERAGEIF calculations.

Excel Formulas Halfwit to Hero

The Tutorials

Setting Conditions

Exercise #1: The school of Excel

Exercise #2: The school of Excel (part 2)