University of South Carolina, Arnold School of Public Health, Dept. of Health Services Policy and Management, HSPM J716

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.

For these first two questions, you can answer with an integer -- a whole number.

What is the slope of your least squares line for clinic 1?

What is the intercept of your least squares line for clinic 1?

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?

What is your t value? This is under the "t coeff=0" label in your spreadsheet.

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

Clinic 2

Clinic 3

For Clinic 3, 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.

Let's check the results of your least squares regression for clinic 3:
Type the results requested for clinic 3 in the small boxes. After typing, press Enter while you're in each box.

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 can make a mistake without realizing it. In a apreadsheet, you can see what you did.
• 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 another cell 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:
  
  You can put this in the same spreadsheet page, like this, or you can put it in a separate tab.
  Fill in the cells I have shaded green with cell references or formulas. (1/N and [X₀ - Xavg]² are the only formulas you have to create in the green-shaded cells.) Then create formulas for the purple cells.

  This kind of layout helps you see and fix 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!