Ah! Something New Has Been Added

During my hiatus over the summer, a few readers were working hard with Excel and were kind enough to send along questions and comments. So, as I catch up on hosting duties here, let's pull out an item from the mailbag and see if we can share some solutions.

Mark2906 asked, "If I need to add more assignments to a particular standard (say 11 tests instead of 7 or 8), how do I alter the formulae for calculating predicted scores?"

from here; Do you know who this is?

Excellent question. No one wants to be stuck manually updating their spreadsheet every time an assignment is added. It's nearly 2013, dammit, and if we can't have flying cars, we can at least have our spreadsheets make an effort to help out around the place.

The good news is that Excel will tag along for most of the adjustments you make to your spreadsheets. Need to copy, move, and paste some data? It will update the formulas automagically.

But that's not quite what Mark has asked us. In many K - 12 classrooms, you don't know exactly how many assignments/pieces of evidence you will be collecting for a particular standard or unit of study. So, here is the simplest way to address this.




Leave Room to Groove
There's no rule that all of the scores in your gradebook have to butt up against one another. When you create a new section for scores, just label more columns than you think you might need. For example:

We're Off on the Road to Excel...

Two sections for standards are shown, each with space for ten assignments. So far, we've recorded five scores for the first standard, and three scores for the second.

To determine the average for Jerry Colonna on the first standard, we can use the following formula: =SUM(C4:L4)/COUNT(C4:L4). We can then just copy the formula down for the other students. Excel will not treat the empty spaces as zeros. Should you need more columns, just insert them after the last assignment and the formula for the average will keep up with you.

If blank columns are occupying too much real estate, you can always hide them.

Peek-a-boo!

Other Ideas to Consider
There are such animals as Dynamic Named Ranges, which expand to fit the data supplied. You can read more about them here or see them in action here. Although they can be very handy, I think they're overkill for this particular problem.

I'm sure there are other solutions, including VBA options. Please share your additional ideas in the comments.