Help me calculate the formulas for Data Analysis add-in

[ExcelJWS]

Hi Guys,


I have a big favour to ask. Within excel there is an add-in called “Data Analysis”, Which can do “Regression”
I am able to get the Coefficients using the following formula as an array (if the ranges are set to be the independent and depend variable data).

=MMULT(MINVERSE(MMULT(TRANSPOSE(M2:O10),M2:O10)),MMULT(TRANSPOSE(M2:O10),Q2:Q10))

The problem I am having is working out how to calculate the:

df

SS

MS

F

Significance F

Coefficients

Standard Error

t Stat

P-value

Lower 95%

Upper 95%



I really want to have a formula for each of these, so that I can understand the mathematics behind what is going on.

I have been using formulas I have found to try and calculate the standard error, however I keep getting the wrong values. Any help would be awesome and would help me understand these very cool mathematical concepts!

Here is the link to the excel file I am talking about:

1.xlsx

Microsoft Excel Workbook

1drv.ms

 

[ExcelJWS]

Hi Guys,

Here is an updated link (I have figured out how to get the SS values)

1.xlsx

Microsoft Excel Workbook

1drv.ms