Data Analytics

1- https://www.youtube.com/watch?v=tfu91nNnhXQ 

2- https://www.youtube.com/watch?v=oaSU1MJtDlE

2. 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.