Moving Points to Percentages

Time for another mailbag question. Jason sends the following about the non-academic behaviors workbook: I am wondering if you know of a way to account for a high number of absences on the report? Obviously, if a student is not in class, they do not receive any points. In essence, that is added in to the total points for the marking period as a zero. Is there a way to modify the point levels on the report that only accounts for days that the student is present? For example, can you put a percentage of days present to equal to a level 1, 2, 3, or 4?

Of course you can. Excel lives to serve, after all.

Let's start by revisiting the original set up. Here's a sample from the "Work Ethics" page.

For each student there are six weeks (M - F) of scores, each based on a four-point scale. Blank cells indicate that the student was absent---no points were earned.

Now, let's hide some of the columns so we can look at formulas:

The Total is a simple sum of the points for the student. The Level is determined by comparing the Total to a range of possibilities ("1" is for less than 59 points, "2" is between 60 and 79 points...).

Jason is interested in a percentage. We can do this by (1) changing the formula for "Total" to one that represents a percentage of points from when the student was present and (2) changing the cut values for the four Levels.

Here is one way to solve the first step:

We still need to keep the sum, but we want Excel to give us the percentage out of the points available, not including days absent. In this grading period, there were 30 days, each with 4 points possible. This gives us the 120 to start the formula in the denominator. Now, we tell Excel to count the number of blank cells in that range, multiply that by the four possible points, and subtract it from 120. We can also tell Excel to format the cell as a percentage.

For the second piece, we only need to update the cuts for each Level. Here is one way to do that:

Note that you will need to express each percentage as a decimal---in this example, .3, .45, .6, and .7.

Beyond this, you would also need to recalibrate the report to use the percentages, as opposed to a raw total of points. But we'll stop here for now.

Need to make other adaptations to the workbooks? Drop me a line and let me know how to help.

The Back End of the Shaggy Dog

I work in tech, and people are forever referring to the "back end" of software, databases, and so on. I find the reference amusing (because inside, I'm 12), but I have to admit that the euphemism fits. The code, meta-data, and other unseen features are not the pretty face that gets shown around. So let's take a look behind the scenes, as it were, of my Shaggy Dog Story with Excel.These instructions are all about MacGuyvering a spreadsheet. While I hope that no one needs them "as is," there are a few tricks here that you might find handy for other projects.

First, I organized my resources on one spreadsheet. Then, I made it into a table. You can do this in Excel by clicking in the cell that will be the uppermost lefthand corner of the table, then using CTRL+T. My table had headers (unit, resource, title description), so I checked that box and hit ENTER. You don't have to turn the data into a table, but (1) I think it makes the formulas easier to manage and (2) you don't have issues with adding records.

Now I have something that looks like this. If you don't like the formatting Excel applies, you can go into the Table options and fuss around with it.

Then, set up your sheet where you want the results displayed. If you're using Excel offline, just use data validation. Don't bother with my "X marks the spot" strategy. However, if you're trying to webify this content, carry on. This is how I set up the demo for the blog post. I used the four column headers (in brown) on this sheet to organize the display.

There are two more small pieces before we get knee-deep with generating a list of items based on user input. First, on the sheet with the list of resources, I set up two IF statements, one for the choices underneath "Unit" and the other for "Category." I needed to tell Excel how to recognize the selections made by the user. Here is how I set up the formula for Unit:

This formula is placed on the other sheet, but since cell references are on this one, I'm showing it here.

Keep in mind that if you're building an offline version using dropdown menus, you can skip this part.

Finally, I set up a COUNTIFS statement based on the user selections. This was also placed on the page with the data set: COUNTIFS(List[Unit],G2,List[Category],G3). This tells Excel to count all of the cells in the table that matched the results of the two IF statements. I named this formula Item_Count. The one I did for work ended up with nearly 30 choices and two extra layers of IF statements...but we won't bother about that now.

An aside here...

I mentioned earlier in the post that converting the data into a table made things simpler for the formulas. Notice that in the COUNTIFS formula that I don't need to state a particular range (e.g. A1:A50). I can just tell Excel to use the table (List) and column header in square brackets (Unit, Resource).

Okay, let's go back to the sheet with the results. We have one more formula to apply...and it's a doozy. Why? We need to generate a list of results based on user input. Our old friends, INDEX and MATCH, are great at identifying the one item that fits a set of criteria...but we need a list. We need Excel to find the first one, the next one, the next one...and so on. Also, the results of each list might not be the same in number--could be one item, could be ten.  I found the basic answer here, if you want to watch a video tutorial; but here's the basic idea for cell B11 shown below. Keep in mind that this is an Array Formula, which means you need to use CTRL+SHFT+ENTER to make it work.

=IF(ROWS($B$11:B11)>Item_Count,"",INDEX(List[Title],SMALL(IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3,ROW(List[Unit])-ROW(List!$A$2)+1),ROWS($B$11:B11))))

If you're hyperventilating after looking at that formula, find a paper bag and come back. We can do this.

The first part of the formula (=IF(ROWS($B$11:B11)>Item_Count,"") means that we want Excel to count the rows from B11 to B11 and compare them to the result of the COUNTIFS formula (named "Item_Count"). If there are more rows than the Item_Count, Excel should leave the cell blank. This part is important because we are going to have a variable number of results based on user input. By including the option for the blank result, we will keep error messages from showing up.

Next, we have the FALSE part of the IF statement---what Excel should do if the Item_Count is smaller than the number of rows. In this case, I want it to INDEX the Resources and display the title based on the following criteria:

• It must match both the Unit and Category as indicated by the results of the IF statements we set up earlier. (IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3)
• It must be the "smallest" (first) item that matches the criteria that is equal to the number of rows we've specified +1:  SMALL(IF(List[Unit]&List[Resource]=List!$G$2&List!$G$3,ROW(List[Unit])-ROW(List!$A$2)+1),ROWS($B$11:B11))

The good news is that you only have to build this formula once. And trust me, you will do your Happy Dance when it works. Use your fill tool to copy the formula down. You can also copy and paste the formula to cell E11 for the description, then change "List[Title]" to "List[Description]" in the formula.

You can download the workbook here, or go back to The Shaggy Dog Story and see how things work in the embedded version.

See? That isn't so Ruff! Ruff!

Bonus Round
The gigantic formula shown above has a lot of different uses. Don't discount it just because it looks scary. For example, think of how you might use it to sort a list of students into different classes (i.e. take a master list and sort it onto multiple spreadsheets). Although Excel is not a database tool, this formula gives you some options to use it that way when you need to.

Ain't Misbehavin'

In the last post, we looked at one way to display information about non-academic behaviors in the classroom---things like work ethic, participation, and attendance. These are student attributes that we value and want to report on, but need to be separated from grades and other measures of learning.

Even though I am sure that I will revisit these ideas and fine tune the data displays over time, I thought I would lift the curtain to reveal the workbook innards. No doubt some of you would like to play, too.

Before we jump into the guts, I want to say that all of the attributes and scales are just examples. We could have a lively discussion about which attributes to measure, how you (or your students) could collect these data, and what the cut scores should be in order to be in the range of performance we'd like to see. These are critical pieces to consider...ones that I hope you ponder and kick around with your colleagues and students. I am going to set them aside in this post and strictly focus on the nuts and bolts.

 

On the Report Sheet, I set up a data validation for the students last names. I started by selecting the range of last names on the Attendance/Grade worksheet. I named the range "LastName." Then, on the Report worksheet in the cell where I wanted the dropdown to appear, I chose Data Validation on the Data tab. In the dialogue box, I selected List from the "Allow" dropdown and then in the "Source" box, I added a formula using my named range. For the "First Name" area on the Report worksheet and the selections at the bottom of each of the other worksheets, I used our old friend, the INDEX/MATCH formula.

Let's move on to a few new tricks. Most teachers keep attendance records by marking who is absent---not who is present. But on my graph, I wanted to chart attendance as a positive measure. There are a few ways to do this. The simplest would be to change the way we keep attendance. Mark students when they're present and just count up the number of days at the end of the grading period. I didn't take the easy way out. Instead, I left each "A" for Absent just where it fell. To get the total number of days present, I used this formula:  =30-(COUNTIF(C6:AF6,"A")). In other words, count the A's in a particular row (in this example, row 6 for Cathy Andrews) and subtract that number from the number of available days in the grading period (30). However, this swap won't work for creating the graphs. I need numbers to work with---not text. But a simple IF statement makes short work of that: =IF(C18=0,1,0), where "C18" is the cell from the INDEX/MATCH result for a particular student.

I also used a nested IF to have Excel convert the number of days attended (or points earned) into a level of performance. For example, here is the one that goes with the worksheets for Work Ethic, Participation, and Behavior:  =IF(AG6<=59,1,IF(AG6<=79,2,IF(AG6<=99,3,IF(AG6>=100,4))))
It looks worse than it really is. (Don't most things?) Notice the repeating "AG6," which is the cell with the total number of points I want Excel to use as a comparison. In the first part of the statement, we tell Excel that if that number is less than or equal to 59, to put a 1 in the cell. If not, Excel moves on to the next part of the statement. Even though we know that "30" is less than or equal to all of the cutoffs in the equation, Excel considers only one at a time.

Shall we talk about the graphs? I used the stacked bar from the charts menu. No doubt some of you out there will want to use a regular bar chart. That is appropriate, too. I picked the stacked bar for two reasons. One was that I wanted to do more than show a comparison. I wanted to illustrate a proportion. A viewer can certainly make that inference with a regular bar chart, but it's a little less work with the stacked bar. My second reason was purely about the aesthetics. I knew I was going to want a line graph in each of the four areas (attendance, participation, work ethic, behavior). The vertical real estate claimed by a bar chart threw off the look of things.

Anyhoo, after you select your data for the graph, don't fall apart of it looks like this:

Simply hit the "Switch Row and Column" option and you will see the stacked bar. After that, you can clean up the graph.

I won't go into detail with the line graph. What I will share is that it does take time to create the labels, standardize the sizes, and get everything in place. Take your time. Don't assume that Excel knows best. It's important to make things look good---otherwise, you are missing your chance to truly communicate with the data.

The radar/star/spider chart is found in the "Other Charts" menu. Again, this chart is not everyone's cup of tea. One of the ed research articles I read mentioned using the chart with college students as a form of feedback---and they hated it. This seemed to be due to the lack of familiarity with reading these types of graphs. We see line graphs and bar charts all the time. Radar charts? Not so much. There are other reasons not to choose this type of chart. For example, it works better with cyclical data. And, the area of the graph becomes distorted with the scale. For every point increase of the scale, the size of the area becomes squared.

I would suggest that if you select this type of chart to use with students (or parents) that you spend some time talking about how to read them. Show them how to look at the connections between the spokes and the area covered by the graph. Why did I choose it, given the difficulty in interpretation? Because the attributes of student performance are not simply about comparing them. There needs to be a broader view of what is happening in a classroom---how is the student performing in light of the overall goals...and more importantly, how might non-academic factors be affecting one another (as well as student learning)? I think that you lose some of these capabilities with a clustered bar graph.

What would you do differently with this workbook and these graphs? Would you choose to put the line graphs all on one chart for easier comparison? Do you like the idea of bar charts better than a stacked bar...and clustered columns instead of the radar graph? How would you organize the workbook?

The Zero Effect

And...we're back. Great to see you all again.

This week, I was building an Excel workbook for teachers. The idea was to create a tool for analyzing student performance on a single assessment. In my case, I was developing for a particular scoring tool (like the example below), but a teacher could make a similar version for a test, allowing one cell per item/point.

At first, this looks like a fairly straightforward affair. I have a list of students down the side. I break down each point of a rubric into the columns in the middle, then have a place to total the points and equate them to a level of performance (e.g., below standard, at standard, above standard) at the end.

Heck, I even added some fancy-schmancy conditional formatting so that the cells with the attribute points would change colour. Note that if more than one point is entered, the cell is highlighted with a border change.

Ooooo. Aahhhh.

WTH, Excel?

But it was too good to last. Excel just had to throw down the gauntlet. Because when I put in the "SUM" formula for the total column, I got a great big zero sitting in the cell---even though there were no numbers in the row to total. I understand that the sum of nothing is nothing...but I don't need the zero there. In fact, as a teacher, I'd prefer that it's blank so I can distinguish between students that haven't done the assessment vs. ones who completed (and bombed) it.

I begged. I pleaded. Please, Excel, let my zeros go. At first, I went in and changed the Options such that zeros didn't show up.

Take that!

Bummer

This sorta fixed things. See, Excel didn't show the zeros, but it acted as if they were still there. This became an issue for the final column. I used a nested IF statement to do the equating (0 - 4 points = below basic, 5 - 6 = basic, 7 - 8 = at standard, 9 - 11 = above standard). And even though no numbers were in any of the preceding columns...and even though I used a "" as the final alternative in the IF statement, Excel didn't care about my personal problem.

So, I set out on a hunt for an answer. Surely I was not the first person in the history of spreadsheets who wanted Excel to not fill its nothingness with zeros. I read many forum posts by people who didn't want zero included for various reasons, but the bottom line was that there was no simple answer to this issue...no magic command to make Excel do my bidding. However, I did find two workarounds that kept invisible zeros at bay.

First dealt with the Total column. I really wanted to leave the option on for showing zero, if a student "earned" it. Originally, I had used the basic SUM function: =SUM(D5:N5). But one YouTube source provided this idea: =IF(COUNT(D5:N5),SUM(D5:N5),""). In other words, tell Excel to count what is in the previous columns...if there's something in them, total them...if not, leave the cell blank. Huzzah!

But this didn't solve the column with the Performance Levels. For this one, I had to use ISBLANK. This function has to be used at the very beginning of a statement. It consists of ISBLANK(value). The "value" refers to a cell. So, by starting my formula with ISBLANK and then following with the nested IF, I knocked out problem #2.

Le Sigh

All was shiny and happy until I got to the point where I wanted to summarize the number of students in each of the performance levels. Here is the formula I was using to total the number of students in Level One: =COUNTIF(P5:P14,"1 = Below Basic")...and the result. I tried ISBLANK at the beginning with the P5:P14 range. It did make the zeros go away, but also kept any other number from appearing. Ditto for the COUNT function at the beginning. And so, my friends, if you have any suggestions, I would love to hear them. (Update: We found the answer! See bottom of post and comments.)

Anyway, here is what the workbook looks like when ready to use:

 

And here is an example of a completed version:

If you want this sample workbook to play around with, you can download it here. Or, if you want to try before you buy, you can play with it in the window below (it may be easier to click the button on the bottom right to view the workbook full screen, but you won't be able to make edits in that window):

Bonus Round

Yes, Virginia, there are graphs for this spreadsheet. They will autoupdate as you add information. There is one graph for the attribute points, so a teacher can look at the distribution and consider where to place additional instruction. The other graph shows the number of students performing in each category. This one could be useful for comparing pre- and post-assessments. Both would be good starting points for reflection on instructional practice.

Update: I did find an answer (similar to one suggested by a commenter) to the problem above. It is included in the updated spreadsheet. Use the same link to download. Thank you to Jamie for the inspiration!

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.

Excel Gradebook for Intermediate Users: Part III, Building Dynamic Graphs

Hey! You came back. I'm glad the IF/INDEX/MATCH combo didn't scare you off, because you're going to need it again for this final tutorial. But hey, you're turning into a real pro with your fancy-schmancy reporting tool. No harm in putting in a bit more practice, right?

If you need some review, have a look at the posts for Part I and Part II of the Intermediate series. Remember that you can download the workbook here, if you want to play the home game. And you can always pop some corn and hang out on my YouTube Channel, should you find yourself in need of seeing things again from the very beginning.

Okay, back to work.

First up is some housekeeping on the Formulas worksheet. We're going to place formulas here for the dynamic graph data. The graphs will appear on the Report. They are considered to be "dynamic" because they can autoupdate based on changes to the other worksheets. We had dynamic data and graphs in our Beginner gradebook, but we just used the space below student scores. Where you put this data is really a matter of personal preference---Excel doesn't care. If I have more than one worksheet feeding a dashboard report, I like the formulas for it on their own worksheet. It helps me stay organized and keeps the workbook looking clean. Feel free to do whatever works best for you.

For each of the four reported standards, I'm going to create a space for the student scores and then on another row, a place for the end of quarter grades. Then, it's time to add the IF/INDEX/MATCH functions.

The formula for the first score for the first standard is =IF(Report!$C$4="P1 Biology",INDEX('P1 Biology'!C$8:C$17,MATCH(Report!$C$6,P1Biology,0)),INDEX('P2 Chemistry'!C$8:C$17,MATCH(Report!$C$6,P2Chemistry,0)))  This is identical to the formulas you used in Part II to display a student's first name and current score, with a couple of minor changes (highlighted below):

=IF(Report!$C$4="P1 Biology",INDEX('P1 Biology'!C$8:C$17,MATCH(Report!$C$6,P1Biology,0)),INDEX('P2 Chemistry'!C$8:C$17,MATCH(Report!$C$6,P2Chemistry,0)))

What's the deal? The "C" column is specific to the column of data from the worksheets. This will change as we move across the sheets with the scores, but the rows (8 - 17) will not. Therefore, there is a "$" symbol before the row numbers to "lock" these and create absolute references. The columns can be relative and move when we fill to the right. Secondly, I've had to add Report! before the cells associated with the class and last name for the student. We didn't have to do this last time because the formulas were on the Report worksheet already. If we don't add it now, Excel will think we're talking about cells on the Formulas worksheet.

Okay, now fill the formula to the right. How many cells?  Well, the first biology standard occupies Columns C - I (7 columns) and chemistry C - J (8 columns). Since we're going to have to draw from one set of dynamic data for our graphs, we need 8 columns total. So, pull your formula over for 7 more columns. Alas, we're going to have alter the last one slightly. Why? Because even though there is data in the "J" column on the biology spreadsheet, it doesn't belong with this data set. Fortunately, this is very simple. Just delete the first INDEX/MATCH function and replace it with "". The "" tells Excel to leave the cell blank. Your formula will look like this:

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

If you're wondering if you can just use a simple INDEX/MATCH function here and skip the whole IF part, well, that would be nice. If you do that, then Excel will give you an error anytime the Report is set for a biology student. You can use another formula to eliminate displaying the error (we'll cover that another time), but why bother when you can just use the double quotes to tell Excel to leave things blank?

You're all set for the first line graph on the Report. Let's get the bar graph set up. For the first standard to be reported, only Chemistry has both 1st and 2nd quarter grades---so it's the only one we need to set up. We can use the same equation we just used (leaving the "" for Biology).

Finally, add "3" in the rows below each score. Remember from our beginner series that this will allow us to add a line for "at standard performance" to each graph. When you've done this, your worksheet will look something like this (depending upon which class/student you have selected):

Get to work setting up the information for the other three standards. Remember to pay attention to how many columns you need and when you might need blank data.

Your last step is to go back to the Report and create the graphs, just as you did for the Beginner workbook. (Don't remember how? Go here.)

If you want to check your work, you can download a finished version of the gradebook here. The last YouTube video in this series is below for your edification and enjoyment. Let me know if you have questions or need help. We'll look at some advanced strategies for building a gradebook soon. Keep practicing!

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.