University of South Carolina, Arnold School of Public Health, Dept. of Health Services Policy and Administration Courses and Curricula, HSPM J716 Updated Jan. 24, 2007

Assignment 2 Answer Checker

Clinic 1

1. there was an error in your Assignment 1 spreadsheet, or
2. you didn't add the new rows correctly, or
3. you typed in the clinic 1 data incorrectly.

If those are both OK, let's test the goodness of fit statistics. Problems here indicate errors in the right half of your spreadsheet.

Is your R-squared between 0.0 and 1.0?

Here is a little more explanation of the R-squared.

What is your t value?

To test the hypothesis that the true slope is 0, you compare your t value with a critical value that you find in the proper column and row in the t table. What is that critical value?

What is your prediction, based on the least squares line, for clinic 1 for the next month, month=12?

If everything is correct to this point, your spreadsheet is good. All you have to do for Clinics 2 is to type or paste its visit data on top of the data for Clinic 1. The spreadsheet will recalculate, and you will have your numbers for Clinic 2. Then, for Clinics 3, you can type or paste its visit data on top of the data for Clinic 2. The spreadsheet will again recalculate, and you will have your numbers for Clinic 3.

Clinic 2

Clinic 3

Prediction confidence interval Scroll down for help with this

• The upper end of the confidence interval is your predicted value plus the result of a big calculation. The lower end of the confidence interval is your predicted value minus that same result.
• The calculation of what to add and subtract from the predicted value are three things multiplied together:
• the appropriate t value from the table (not the t value from your spreadsheet)
• s, the regression's standard error
• a big square root
• The big square root has under it:
• 1/N plus
• a messy fraction plus
• 1
• The messy fraction's
• numerator is the X value for which you are predicting, 12 in this case, minus the mean of the X's. You square that difference.
• denominator is the sum of squared X deviations, which is in your spreadsheet.
• You are much better off doing this calculation in a spreadsheet, rather than by hand. If you calculate by hand, you may be off solely because you are not keeping enough decimal places, or because of an arithmetic error.
• In your spreadsheet, you don't need to cram all of the big formula into one cell. Instead, use several cells. For example, you can put the individual parts of the expression under the square root in three separate cells, then add them up in a fourth cell, and take the square root in a fifth cell. Then use more cells to multiply by the other pieces.

  A good way to do that is to take advantage of the spreadsheet's two-dimensional layout to use spatial reasoning. Lay out the elements of the equation in the same relative positions as in the formula itself.

  Here is the formula:
  

  Here is a corresponding spreadsheet layout:
  
  Fill in the shaded cells with the appropriate formulas and cell references.

  This kind of layout helps you avoid errors, and it would help you remember what you did if you came back to this spreadsheet in six months.

Thanks for using the answer checker!