I'm just going to give you a little bit of a preview for the top hat consolidator project. This is the starter file. We have a dashboard here. This is what you're going to have to fill in. You're going to have to fill in the name. The name should be drop-down menus. If we go to the data tab for all of the students starting in A2 all the way down to A51, you're going to have to come up with formulas for the raw score, out of the percentage, the percentage when the lowest five scores are dropped. You're also going to need this to populate with the classes, and then have the students score, the class average on that particular class, and then up here, we've got a question lookup. The user can select the class, the question, and it'll give the class average. This is the template, the starter file, I'll show you a working file here in a minute. On the second tab, the Data tab. This is all the data. We've got our students, we've got the various classes. You notice that each assignment has a class followed by a colon and a space, and then the name of the question. For example, class 3 had three questions. We had a question called negation, a question of sine, and a question called data table. Some classes only have one question. For example, class 4 only has a VLOOKUP question. Now let me show you what you're trying to. This is a working project we have from a drop-down menu. We have all of those students in the Data tab, in the column A of the Data tab. The raw score is just the sum over all of the various questions throughout the semester. It's the raw score so it's the sum for that student. Out of, is the total number of questions. Going across the top here, the total number of questions to date, multiplied by 5, that's the total points. The percentage is simply the raw score divided by out of. Now what you're going to have to do is you're going to have to subtract the lowest five scores from each student or the student that the user selects up here. I've got a formula for how to do that in the problem statement, the project description. Lower down here, you're going to have to have all of the classes to date. Up to that point for all of the data collected so far in the Data tab. We've got class 1 up through class 20. This is the average for this student. The student that's selected up here in the drop-down menu for class 1. Now it's important, for example, class 3 had three questions. It's important that you're averaging for those three questions for that student. It's not just the first question, it's an average over all questions for that class period. Same thing for class average. You have to average the entire class, all students for all questions in that class period. This right here is, I think the hardest part. I have a screencast, a hint for averaging the columns of dynamic arrays. Finally up here, if a question lookup, so you should be able to select the class. Now when you change this, this is conditional drop-down menus and I think a lot of you will have already done projects earlier in the course, where you reset the conditional formatting here, the conditional data validation. This is a VBA script, so this file is actually going to be a macro enabled workbook. But depending upon the class, the user can select the question. Let's just select this iteration question and averages over all students the performance on that question. Now this has to be dynamic. In other words, if I go to the Data tab here, and I add in a new student, so I've added in my name with a bunch of scores. If you go back over to dashboard, we should be able to look up my name, and it'll automatically update the newly added students scores, so it's dynamic. We can also add in a new column of data. This is a new assignment, class 21. The question name was plotting. I've added that into our original data. It's dynamic, in that, if I now look down here at all the classes, we see class 21 has been added into the class average on that newly added question. We can also go up here at the top. It should automatically be added here, class 21, and we only had one question there named plotting, but it gives the class average on that particular question. Hopefully this screencast gives you a better idea of what's you're expected to do for the top hat consolidator project. Have fun and good luck.
