Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Regression P-Value Function
When using the data analysis toolpack to carry out regression, you enter a
Y-input range and an X-input range. The result is a lot of data, including a p-value, contained in a number of tables. My question is, is there a function which makes it possible to return only the regression p-value in a single cell for the X and Y data range? If so, it would then be possible to drag the function down the sheet to return a p-value for a number if different Y data ranges without having to produce a large number of tables just to get a p-value for each one. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Regression P-Value Function
All output from the ATP regression tool is either given directly by LINEST,
or can be easily computed from LINEST output. Assuming that you want one of the p-values that are labeled "p-value", rather than the one labeled "Significance F", then the p-value is given by =TDIST(t,dfe,2), where t is the corresponding "t Stat" and dfe is the "Residual" "df" from the table above. The quantity labeled "t Stat" is the corresponding "Coefficent" divided by its "Standard Error". The ATP "Coeffients" and "Standard Error" values are given in rows 1 and 2 of the output of the LINEST function, and dfe is given in row 4, column 2 of the LINEST output. You can use the INDEX function to pick off these values to get the p-value using a single cell, like =TDIST( INDEX(LINEST(ydata,xdata,const,TRUE),1,col)/ INDEX(LINEST(ydata,xdata,const,TRUE),2,col), INDEX(LINEST(ydata,xdata,const,TRUE),4,2),2) where col is the column number of the LINEST output that gives the desired coefficient. Jerry "BG" wrote: When using the data analysis toolpack to carry out regression, you enter a Y-input range and an X-input range. The result is a lot of data, including a p-value, contained in a number of tables. My question is, is there a function which makes it possible to return only the regression p-value in a single cell for the X and Y data range? If so, it would then be possible to drag the function down the sheet to return a p-value for a number if different Y data ranges without having to produce a large number of tables just to get a p-value for each one. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Regression P-Value Function
Thanks for your answer Jerry,
It was a great help. I was looking for the X variable p-value, and just before you replied to my question I found that it could be obtained using the FDIST function. FDIST(x, degrees of freedom 1, degrees of freedom 2) x is calculated using the degrees of freedom 2 value and, the R squared (RSQ) value of the x and y data as follows: DOF2-RSQ/(1-RSQ) DOF2 is the number of Y values minus 2 DOF1 is the number of groups minis 1 I have also just noticed that the X variable p-value is the same as the Regression Significance F value. Thanks again for you help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Regression function in 2007, Analysis Tool Pak | Excel Discussion (Misc queries) | |||
The multiple regression tool - linear or non-linear regression? | Excel Discussion (Misc queries) | |||
regression | Excel Discussion (Misc queries) | |||
Linear Regression using the TREND function | Excel Worksheet Functions |