Data Analytics

Data Analytics

Using the techniques/concepts shown in the videos, create an excel spreadsheet…that contains headers in the first row for “Scenario” in column A, “Exercises” in column B, “Chapter Quizzes” in Column C, 4 exams in columns D, E, F, G, then “Combined Exams” in Column H, “Mini-Project” in Column I, “Total Percent” in Column J, and “Earned Grade” in Column K.
A few pointers:

Column H = you want this to equal the highest 3 of 4 exams percentages from Columns C, D, E, F. There is more than one way to do this. Here is a simple one… if we are in Cell H2, you would type in the formula: =(sum(D2:G2)-min(D2:G2))/3

What that formula does is add up all four percentages, then subtract the lowest score, and then divide the result by 3 to get an average percentage across the top three scores. Remember the double parentheses (as shown) so it does it correctly.

Column J: to get total percent, you want to rescale the percentages across this database so they are correctly weighted according to the class syllabus. So Cell J2’s formula could be: =B2*.05+C2*.05+H2*.75+I2*.15

Column K: to get the correct major grade letter to automatically merge in, you need to use a set of nested “if-then” statements. An “if-then” statement in excel has three parts: first part is the lookup test, second part is what to do if it is true, third part is what to do if it is false). K2 could read: =if(J2<.6,”F”,if(J2<.7,”D”,if(J2<.8,”C”,if(J2<.9,”B”,”A”))))

Notice that each third part (if false) is the next investigation level so it becomes ‘nested’.

3. Create five different rows of data in Rows 2 to 6 containing different scenarios (i.e., different percentages in scores) showing how different scores would combine to arrive at a total forecasted score–pick any values you want between 70% and 100% to go in them.

4. Save the excel spreadsheet. Upload a copy of it here for participation grading.
Note: If you become stuck, reread the instructions and rewatch the videos. If needed, add questions to the class forum on the topics you are working on mastering.

Last Updated on February 11, 2019

Don`t copy text!
Scroll to Top