Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Regression Function in VBA Macro
Hi,
I wanted to use the regression functionality of Excel in a VBA code to use it on specific data which has more than one independent variables entered by the user but I haven't been able to find the correct syntax to be used to call the function. I know it is something related to "ATPVBAEN.XLAM!Regress" or something but if somebody could give me the correct syntax with all the options like plot, etc then that would be really helpful. I specifically need to make a VBA project out of it so using the addin manually is of no use to me, I wish to call the functionality from my VBA code to make it automatic for the user. Please let me know the exact syntax of this function. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
"Shreyans Jain" wrote in message Hi, I wanted to use the regression functionality of Excel in a VBA code to use it on specific data which has more than one independent variables entered by the user but I haven't been able to find the correct syntax to be used to call the function. I know it is something related to "ATPVBAEN.XLAM!Regress" or something but if somebody could give me the correct syntax with all the options like plot, etc then that would be really helpful. I specifically need to make a VBA project out of it so using the addin manually is of no use to me, I wish to call the functionality from my VBA code to make it automatic for the user. Please let me know the exact syntax of this function. These are the arguments Regress(inpyrng, inpxrng, constant, labels, confid, soutrng, residuals, sresiduals, rplots, lplots, routrng, nplots, poutrng) Apart from the first Y-input range argument the others are optional. Call like this Application.Run "atpvbaen.xlam!regress", Range("B2:B19"), Range("A2:A19") It's easier, particularly in development, to set a reference in tools/refs to atpvbaen.xla, then you can simply use Regress as if it was a builtin function and get the intellisense, simply regress Range("B2:B19"), Range("A2:A19") It's not always practical to keep the reference when distributing, if in doubt remove it and use the app-run method In Excel, Data, Analysis data (at the end), Data Analysis, Regression (OK) Take a screen shot of the dialog (alt-prtScn) and paste it on a sheet. You should be able to recognise the Regress arguments on the dialog. You could build your own form along the lines of the dialog, with inputbox's, refedit's etc for user to pick and apply the inputs. Compare your results with the builtin results. There's also RegressQ though I'm not sure what the difference is. Regards, Peter T |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
"Peter T" wrote in message In Excel, Data, Analysis data (at the end), Data Analysis, Regression (OK) Forgot to say the Analysis toolpack must be installed as an addin to see this on the ribbon. PT |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
hi,
the Regression tool uses the worksheet function LINEST http://office.microsoft.com/en-us/ex...010342762.aspx http://office.microsoft.com/en-us/ex...005209155.aspx x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), False) isabelle Le 2014-05-27 02:58, Shreyans Jain a écrit : Hi, I wanted to use the regression functionality of Excel in a VBA code to use it on specific data which has more than one independent variables entered by the user but I haven't been able to find the correct syntax to be used to call the function. I know it is something related to "ATPVBAEN.XLAM!Regress" or something but if somebody could give me the correct syntax with all the options like plot, etc then that would be really helpful. I specifically need to make a VBA project out of it so using the addin manually is of no use to me, I wish to call the functionality from my VBA code to make it automatic for the user. Please let me know the exact syntax of this function. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
"isabelle" wrote in message hi, the Regression tool uses the worksheet function LINEST http://office.microsoft.com/en-us/ex...010342762.aspx http://office.microsoft.com/en-us/ex...005209155.aspx x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), False) isabelle Indeed the tool uses LinEst but can get more stats like this x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), True, True) Returns a 5x2 array of stats as shown on the help page you referred to. The regression tool returns other stats that LinEst doesn't, though LinEst can also return the polyniomial cooefficients of a 'best fit' curved line. Regards, Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
ok, for REGRESS macro
REGRESS(inpyrng, inpxrng, constant, labels, confid, soutrng, residuals, sresiduals, rplots, lplots, routrng, nplots, poutrng) Inpyrng is the input range for the y-values (dependent variable). Inpxrng is the input range for the x-values (independent variable). Constant is a logical value. If constant is TRUE, the y-intercept is assumed to be zero (the regression line passes through the origin). If constant is FALSE or omitted, the y-intercept is assumed to be a non-zero number. Labels is a logical value. If labels is TRUE, then the first row or column of the input ranges contain labels. If labels is FALSE or omitted, all cells in inpyrng and inpxrng are considered data. Microsoft Excel will then generate the appropriate data labels for the output table. Confid is an additional confidence level to apply to the regression. If omitted, confid is 95%. Soutrng is the first cell (the upper-left cell) in the output table or the name, as text, of the new sheet to contain the summary output table. If FALSE, blank, or omitted, places the summary output table in a new workbook. Residuals is a logical value. If residuals is TRUE, REGRESS includes residuals in the output table. If residuals is FALSE or omitted, residuals are not included. Sresiduals is a logical value. If sresiduals is TRUE, REGRESS includes standardized residuals in the output table. If sresiduals is FALSE or omitted, standardized residuals are not included. Rplots is a logical value. If rplots is TRUE, REGRESS generates separate charts for each x versus the residual. If rplots is FALSE or omitted, separate charts are not generated. Lplots is a logical value. If lplots is TRUE, REGRESS generates a chart showing the regression line fitted to the observed values. If lplots is FALSE or omitted, the chart is not generated. Routrng is the first cell (the upper-left cell) in the residuals output table or the name, as text, of the new sheet to contain the residuals output table. If FALSE, blank, or omitted, places the residuals output table in a new worksheet. This argument is for compatibility with Microsoft Excel 4.0 and is ignored in Microsoft Excel version 5.0. Nplots is a logical value. If nplots is TRUE, REGRESS generates a chart of normal probabilities. If nplots is FALSE or omitted, the chart is not generated. Poutrng is the first cell (the upper-left cell) in the probability data output table or the name, as text, of the new sheet to contain the probability output table. If FALSE, blank, or omitted, places the probability output table in a new worksheet. isabelle Le 2014-05-27 12:55, Peter T a écrit : Indeed the tool uses LinEst but can get more stats like this x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), True, True) Returns a 5x2 array of stats as shown on the help page you referred to. The regression tool returns other stats that LinEst doesn't, though LinEst can also return the polyniomial cooefficients of a 'best fit' curved line. Regards, Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
On 27/05/2014 17:55, Peter T wrote:
"isabelle" wrote in message hi, the Regression tool uses the worksheet function LINEST http://office.microsoft.com/en-us/ex...010342762.aspx http://office.microsoft.com/en-us/ex...005209155.aspx x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), False) isabelle Indeed the tool uses LinEst but can get more stats like this x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), True, True) Returns a 5x2 array of stats as shown on the help page you referred to. The regression tool returns other stats that LinEst doesn't, though LinEst can also return the polyniomial cooefficients of a 'best fit' curved line. Be aware that it is only marginally numerically stable if you try to fit anything more than a quadratic on data with a x axis offset. By comparison the polynomial fit in the charts is done correctly but doesn't by default display its answers with enough significant digits. -- Regards, Martin Brown |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
"Martin Brown" wrote in message On 27/05/2014 17:55, Peter T wrote: "isabelle" wrote in message hi, the Regression tool uses the worksheet function LINEST http://office.microsoft.com/en-us/ex...010342762.aspx http://office.microsoft.com/en-us/ex...005209155.aspx x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), False) isabelle Indeed the tool uses LinEst but can get more stats like this x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), True, True) Returns a 5x2 array of stats as shown on the help page you referred to. The regression tool returns other stats that LinEst doesn't, though LinEst can also return the polyniomial cooefficients of a 'best fit' curved line. Be aware that it is only marginally numerically stable if you try to fit anything more than a quadratic on data with a x axis offset. By comparison the polynomial fit in the charts is done correctly but doesn't by default display its answers with enough significant digits. What still! I just tried a 5 order with 20 'x' values and compared Linest with the trendline equation (numberformat 11dp) and both were the same to within 10dp. First tested with a perfect equation and various values for the coefficients y = a + bx + cx^2 + dx^3 + ex^4 + fx^5 Then added some random irregularity with y up to +/-5% deviation from the norm, again Linest and the equation formula returned same coefficients to within 10dp. I know that's not a 'stressful' test but could you illustrate when Linest becomes unreliable and/or returns different coeff's to a high precision chart formula. Regards, Peter T |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
"Peter T" wrote in message I know that's not a 'stressful' test but could you illustrate when Linest becomes unreliable and/or returns different coeff's to a high precision chart formula. Hmm, with a bit more testing I am getting discrepancies between Linest and the chart formula, but it's the chart formula that's wrong (particularly with the first x is not increasing from 1), Linest still returning same coef's as the original formula (with no artificial deviation). PT |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
On 28/05/2014 21:52, Peter T wrote:
"Martin Brown" wrote in message On 27/05/2014 17:55, Peter T wrote: "isabelle" wrote in message hi, the Regression tool uses the worksheet function LINEST http://office.microsoft.com/en-us/ex...010342762.aspx http://office.microsoft.com/en-us/ex...005209155.aspx x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), False) isabelle Indeed the tool uses LinEst but can get more stats like this x = Application.LinEst(Range("A2:A5"), Range("B2:B5"), True, True) Returns a 5x2 array of stats as shown on the help page you referred to. The regression tool returns other stats that LinEst doesn't, though LinEst can also return the polyniomial cooefficients of a 'best fit' curved line. Be aware that it is only marginally numerically stable if you try to fit anything more than a quadratic on data with a x axis offset. By comparison the polynomial fit in the charts is done correctly but doesn't by default display its answers with enough significant digits. What still! I just tried a 5 order with 20 'x' values and compared Linest with the trendline equation (numberformat 11dp) and both were the same to within 10dp. First tested with a perfect equation and various values for the coefficients y = a + bx + cx^2 + dx^3 + ex^4 + fx^5 Then added some random irregularity with y up to +/-5% deviation from the norm, again Linest and the equation formula returned same coefficients to within 10dp. I know that's not a 'stressful' test but could you illustrate when Linest becomes unreliable and/or returns different coeff's to a high precision chart formula. Add an offset of about 10000 to all the x values - this makes the condition number of the problem much higher for a naive code. It is the sort of thing that can happen when the x values are dates for example. When they first released XL2007 they broke the polynomial fit in charts to make it agree with a well known PC package with the same defect! -- Regards, Martin Brown |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
On 28/05/2014 22:29, Peter T wrote:
"Peter T" wrote in message I know that's not a 'stressful' test but could you illustrate when Linest becomes unreliable and/or returns different coeff's to a high precision chart formula. Hmm, with a bit more testing I am getting discrepancies between Linest and the chart formula, but it's the chart formula that's wrong (particularly with the first x is not increasing from 1), Linest still returning same coef's as the original formula (with no artificial deviation). I haven't tested it very recently but normally the chart formula is done right and LinEst is far too crude to solve it correctly. You can fix the fault in LinEst by rescaling your problem to control the condition number so that the x-axis runs from -1 to 1. The problem arises when people fit a trend against date/time values. (I don't approve of them fitting high order polynomials to begin with but if they do it is incumbent on the code to return a true best fit!) Sample data that shows it should be in the thread https://groups.google.com/forum/#!search/excel$20polynomial$20fit$20/microsoft.public.excel.worksheet.functions/QWVh_4C2gkA/cyYk3ah6n_kJ -- Regards, Martin Brown |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
"Martin Brown" wrote in message On 28/05/2014 22:29, Peter T wrote: "Peter T" wrote in message I know that's not a 'stressful' test but could you illustrate when Linest becomes unreliable and/or returns different coeff's to a high precision chart formula. Hmm, with a bit more testing I am getting discrepancies between Linest and the chart formula, but it's the chart formula that's wrong (particularly with the first x is not increasing from 1), Linest still returning same coef's as the original formula (with no artificial deviation). I was stupid, I should have used an XY chart if Xs are not 1,2,3 etc, then Linest & the chart formula return same. Otherwise the chart formula assumes X increments in intervals of 1 starting from 1. I haven't tested it very recently but normally the chart formula is done right and LinEst is far too crude to solve it correctly. You can fix the fault in LinEst by rescaling your problem to control the condition number so that the x-axis runs from -1 to 1. The problem arises when people fit a trend against date/time values. (I don't approve of them fitting high order polynomials to begin with but if they do it is incumbent on the code to return a true best fit!) Date values as Xs counting from year 1901 are surely unrealistic if using as Xs in a high order polynomial, unless the first date is say early in 1901. However Time values don't seem to be a problem (should be in an XY chart). Sample data that shows it should be in the thread https://groups.google.com/forum/#!search/excel$20polynomial$20fit$20/microsoft.public.excel.worksheet.functions/QWVh_4C2gkA/cyYk3ah6n_kJ I tested with his sample data and got identical results with Linest and the chart formula (using an xy chart), also the same as his chart formula. However his Linest results were obviously different - then I noticed he was using Excel 2000. So I dug up my 2000 and reproduced his Linest results. IOW Linest appears to have been fixed in 2007 (if not 2003?). I did say "what still" in my earlier reply, just noticed that thread is also dated 2000, apart from lets say unreasonable data do you still think Linest is unreliable? Regards, Peter T |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Regression Function in VBA Macro
"Martin Brown" wrote in message On 28/05/2014 21:52, Peter T wrote: "Martin Brown" wrote in message On I know that's not a 'stressful' test but could you illustrate when Linest becomes unreliable and/or returns different coeff's to a high precision chart formula. Add an offset of about 10000 to all the x values - this makes the condition number of the problem much higher for a naive code. It is the sort of thing that can happen when the x values are dates for example. But is that a realistic scenario, 10000^5 is 10E+20 which would imply a decimal cooefficient with at least 10 to 15 leading zeros. When they first released XL2007 they broke the polynomial fit in charts to make it agree with a well known PC package with the same defect! In light testing I still can't reproduce any discrepancies. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Linest function for polynomial regression on horizontal range | Excel Programming | |||
Regression P-Value Function | Excel Worksheet Functions | |||
Problem with Regression function in 2007, Analysis Tool Pak | Excel Discussion (Misc queries) | |||
Custom function for max residual from linear regression | Excel Programming | |||
Linear Regression using the TREND function | Excel Worksheet Functions |