Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Linest function for polynomial regression on horizontal range Herman[_2_] Excel Programming 0 December 4th 11 02:28 PM
Regression P-Value Function BG Excel Worksheet Functions 2 January 16th 09 02:28 PM
Problem with Regression function in 2007, Analysis Tool Pak Lauren273 Excel Discussion (Misc queries) 2 May 25th 08 10:00 PM
Custom function for max residual from linear regression Bob Nelson Excel Programming 7 July 28th 06 03:14 PM
Linear Regression using the TREND function scarlett1 Excel Worksheet Functions 2 May 26th 06 03:04 PM


All times are GMT +1. The time now is 01:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"