Use the attached template to develop a spreadsheet model that can calculate the covariance and correlation of any two samples with n<=10. The first sheet, “Template”, is laid out and it will be your guide while developing the spreadsheet model, while the second sheet, “Test Data”, has three example data sets that you can use while testing your model. Your model is considered complete and working if I can copy and paste data sets into the model you’ve developed on the Template worksheet, and your model will calculate the correct Covariance and Correlation Coefficient. Again, the data I have provided on the Test Data worksheet is for your testing purposes, so to help you I’ve provided the correct Covariance and Correlation for each test set, but I will be using different test sets than these when I evaluate your model!
To be clear, I want you to develop a spreadsheet model that reflects what you would do by hand if I made you solve the problems by hand, paper-and-pencil style, so you should build out the formulas the long way as I did in the example, not using the =Covariance.S and =Correl functions. Also, to prevent you from cheating and just typing in the answers somewhere, I’ll be using different test sets when I evaluate your spreadsheet than the ones I gave you to test it. So, the grading of your model will be simple: if your spreadsheet avoids shortcuts like =Correl and calculates the correct values for the data I put in, you get full credit! As before, I’ve provided the rubric below for additional details.
WARNING: you MUST use Microsoft Excel specifically; if you use Numbers (the Mac equivalent) for example, I won’t be able to open the file. If you don’t have Microsoft Office, it can be downloaded for free at this website.
Follow All the Directions Correctly and exactly as it says.
Please Do Good and Correctly – IF DONE CORRECTLY , THERE IS BIG TIP !!!