Excel Gradebook for Intermediate Users: Part II, Using the IF Function

In Part I of this Intermediate series for Roll Your Own Gradebook, we took some time to get organized. We added a worksheet to manage some of the ranges and formulas we need and created two data validation lists---one for sorting our information by class period and another for sorting by the last names of students in each class. Now it's time to get going on the rest of the report.

We'll get started with the basic version of the "IF" formula first. An "IF" formula tells Excel what to do based on whether the comparison is true or false. The first one we'll do is in cell B13. The formula will look like this: =IF($C$4="P1 Biology",Formulas!B2,Formulas!C2)  We are telling Excel to compare the information in cell C4 with the text "P1 Biology." If these two items are the same (true), then Excel should use the information from the first cell with our biology standards. But, if "P1 Biology" isn't selected (false), it automatically picks the first chemistry standard. We don't have to tell Excel something special just for chemistry. Because we just have two options at this point, we can just go with biology or not biology as options. (Want to know what to do if you have more than two choices? We'll tackle that in the Advanced Gradebook in a future post.)

Now, click on the bottom righthand corner of the cell and fill down to complete the three cells below.

You're ready for the big leagues now. We are going to combine our brand-new knowledge of the "IF" statement with our old knowledge of the "INDEX" and "MATCH" functions. Go back up to cell G6---the space for a student's first name. Don't freak out when you see this next formula, okay?

Are you sitting down?

I promise, everything will be fine.

You trust me, don't you?

Deep breath. Here we go:

=IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0)))

Congratulations---you've just put the INDEX/MATCH formula from the beginner's series into the IF statement you used above. You're telling Excel that if the report is for P1 Biology, then it should index the list of first names on the P1 Biology worksheet and match them by using the last name shown on the dashboard...but if the report is not for P1 Biology, it should use the list of chemistry students. I know. It looks like a lot, but just take each piece at a time---little bites until, Lo and Behold!, you've eaten the whole elephant.

Now, take another deep breath and use the same formula to build the "Current Score" column for your report. The only things you have to change in the entire formula above are highlighted below. You just need the new column letters from the worksheets.

=IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0)))

Whew!

That wasn't so bad, was it? (I feel like Mr. Rogers. "I knew you could!")

Watch the tutorial below to see the formulas in action. Come back next time for the coup de grâce: using IF/INDEX/MATCH to create a dynamic table for our graphs.