MrExcel Publishing
Your One Stop for Excel Tips & Solutions

HELP!!! Regression

Posted by Michelle on December 12, 2001 8:49 PM

I need help. I just need to find the estimated intercept and estimated slope for the number of patients in year 6 and the cost in year 6 for this data set:
year Cost #patients
1 35 20
2 50 32
3 70 50
4 80 60
5 92 72
i also need to do a t-test on the slope (Ho and Ha, test value and crit value)

Posted by Steven Knight on December 13, 2001 2:10 AM

I presume you are quite well versed in statistics, you can use m = Sxy/Sxx and then use C= (MU)Y - (m*(MU)X) This will give you a gradient m and a slope C.

Posted by Mark W. on December 13, 2001 11:07 AM

See the Help index topic for the LINEST worksheet
function. If your data is in cells A2:C6...

1. Select 2 cells in adjacent columns (e.g., E2:F2)
2. Enter a single array formula, {=LINEST(C2:C6)}.
3. The slope and y-intercept will be displayed in
cells E2 and F2 respectively.

Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.