Excel Gradebook for Advanced Users: Nested IF Statements

And now, the grand denouement of the Roll Your Own Gradebook series. If you're new around these parts, you can catch up on the series with

• Beginners, Part I: Organizing Data
• Beginners, Part IIa: INDEX and MATCH
• Beginners, Part IIb: Sparklines
• Intermediate, Part I: Ranges and Validation Lists
• Intermediate, Part II: Using the IF Function
• Intermediate, Part III: Building Dynamic Graphs

Each comes with a screencast tutorial and links to both the "before" and "after" version of the gradebooks. Make classroom data dance to your tune and shake its little moneymaker for your stakeholders using Excel.

In the intermediate series, we integrated two sets of data into one report. But let's face it, most teachers do not have the luxury of teaching two classes or subjects. If you're an elementary teacher, you're juggling multiple subject areas and assessment sources. Secondary teachers have several class periods. You can still use a single reporting tool. You just have to master one more formula: the nested IF statement.

You already know how to construct an IF statement. In fact, if you've been following this series, you've already done a fairly complicated one involving the addition of INDEX and MATCH. This time, we're just going to expand that to "nest" (read: embed) a couple more IF statements in the formula. You can download the gradebook for this tutorial here.

In this workbook, there are 4(!) classes. We still have biology and chemistry from the intermediate series, and now we have physics and earth science. We also have the same Report we've been using since the very beginning. I've already added the named ranges for you in the Formulas worksheet. You will not need to update the data validation lists for the report.

In the Intermediate gradebook series we used the following formula to fill in the cell marked in yellow: =IF($C$4="P1 Biology",Formulas!B2,Formulas!C2)

We had two classes, which fit perfectly into the plain Jane IF statement: a place to tell Excel to look if the first part ($C$4="P1 Biology") was true (the first cell with a biology standard), and a place to look if the first part was false (the first cell with a chemistry standard).

Now, we have to tell Excel to look in one of four places. Oh noes! It doesn't fit in an IF statement anymore. But we can fool Excel with an Inception-style solution: an IF statement within an IF statement within an IF statement.

Here's what it will look like: =IF($C$4="P1 Biology",Formulas!B2,IF($C$4="P2 Chemistry",Formulas!C2,IF($C$4="P3 Physics",Formulas!D2,Formulas!E2)))

Here's what it means. The red text below shows the beginning of our IF: the statement to evaluate ($C$4="P1 Biology") and what to do if that is true (Formulas!B2), but instead of directing Excel to another cell for a false statement, we feed it another IF. This is the statement in purple, which leads to another false statement in blue. Once you have this formula in place for the report, use your fill down option (look for the crosshairs when your cursor is placed in the bottom righthand corner of the cell) to complete the three cells below.

That's not so bad, is it? Keep in mind that you can have up to 7 IFs nested in a single statement. If you have more than 7 options, you will have to use a workaround that we can talk about in another post.

Okay, now you need to take a deep breath and modify the IF/INDEX/MATCH statement from the Intermediate series of tutorials. The bad news is that this is going to look a little scary. The good news is that you only have to do it once. After that, you can copy and paste the formula into other cells, then just edit the columns you need.

Let's start with the box for the student's first name, shown in yellow in the diagram shown below.

In the Intermediate series, we used the following formula: =IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0))). This time, due to juggling four classes, you're going to have to manage a much larger formula. You've trained for this. You can do it.

Ready? Okay!

=IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),IF(C4="P2 Chemistry",INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0)),IF(C4="P3 Physics",INDEX('P3 Physics'!B8:B17,MATCH(C6,P3Physics,0)),INDEX('P4 Earth Science'!B8:B17,MATCH(C6,P4EarthScience,0)))))

It's not as bad as it looks. See the pattern? IF, INDEX, MATCH...IF, INDEX, MATCH...Lather, Rinse Repeat. And you know what? For all the "Current Scores" cells, you only need to change the highlighted columns in the formula.

When you're ready to fill in the formulas for the dynamic graphs, you can copy and paste the formula again, but you will have to make a couple of adjustments. Excel won't know that cells C4 and C6 refer to the Report worksheet unless you tell it. To make the fill go more easily, make some attributes absolute (with the $) so that Excel only changes the columns you want it to change. Here is an example for cell H2 of the Formulas sheet:







=IF(Report!$C$4="P1 Biology",INDEX('P1 Biology'!C$8:C$17,MATCH(Report!$C$6,P1Biology,0)),IF(Report!$C$4="P2 Chemistry",INDEX('P2 Chemistry'!C$8:C$17,MATCH(Report!$C$6,P2Chemistry,0)),IF(Report!$C$4="P3 Physics",INDEX('P3 Physics'!C$8:C$17,MATCH(Report!$C$6,P3Physics,0)),INDEX('P4 Earth Science'!C$8:$C$17,MATCH(Report!$C$6,P4EarthScience,0)))))

Also remember that not every class has the same number of scores to report for every standard. For example, cell O2 of the Formulas sheet would use

=IF(Report!$C$4="P2 Chemistry",INDEX('P2 Chemistry'!J$8:J$17,MATCH(Report!$C$6,P2Chemistry,0)),IF(Report!$C$4="P3 Physics",INDEX('P3 Physics'!J$8:J$17,MATCH(Report!$C$6,P3Physics,0)),""))

because only the Chemistry and Physics worksheets have assignment scores in Column J (Biology and Earth Science have median scores in those columns). The "" at the end of the formula tells Excel to leave the cell blank if it is not a Chemistry or Physics class. If you're still a little confused about this part, you might want to go back and review the last part of the Intermediate series.

Want to check your work or just don't want to hassle with building the workbook? You can download a copy of the completed version of this gradebook (with all of the formulas and graphs).

Here is the YouTube version of the tutorial. Enjoy!



Don't be sad that we have reached the end of the gradebook build. There will be plenty of new permutations to explore. (I've already had an inquiry about adding in the Power Law formula.) We will also look at other types of dashboard reports to summarize activity in your classroom. Keep leaving your ideas and suggestions in the comments and we'll keep on trucking with this.

Bonus Round
Does the thought of managing these formulas make you queasy? Remember that you can just use what's included with the Beginner's series and create a separate workbook for every class or subject. Or, if you have more than one period of the same subject, list all students in the same worksheet. This will decrease the number of IF statements you need to develop.