Paging David Braden Re: Regression

  • Thread starter Thread starter Kevin Sprinkel
  • Start date Start date
K

Kevin Sprinkel

David,

A responder to a recent post referred me to your online
code that automates fitting a new x value to either a
quadratic or logarithmic trendline. It seems to be tailor-
made to my application, however, I've received a #NAME?
error on the following call:

=TLcoef("Regression",1,"KnownY",1)

Can you identify where I've strayed?

Thank you.
Kevin Sprinkel
 
A responder to a recent post referred me to your online
code that automates fitting a new x value to either a
quadratic or logarithmic trendline. It seems to be tailor-
made to my application, however, I've received a #NAME?
error on the following call:

=TLcoef("Regression",1,"KnownY",1)
Click to expand...

Where did you put the VBA code? Unless it's in a general VBA module, you'll get
#NAME? errors if you call it from worksheet formulas. See

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
I'm not David but my guess is that you must replace KnownY by the range
holding your y-values, and Regression by the x-values (?)
Why not use LINEST ?
 
Thanks, Bernard, for your response.

"KnownY" is the name of the y-range, "Regression" is the
name of the worksheet per the function syntax. Linest
falls short of the dynamic function I need, and only works
for linear trendlines. I also require log and quadratic.
 
Where did you put the VBA code? Unless it's in a general
VBA module, you'll get
#NAME? errors if you call it from worksheet formulas. See
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...

Harlan,

Thank you for your response.

The code is in a module in my personal.xls worksheet
loaded on startup. The above link suggests that I will
need to "include the project library (i.e.personal.xls!)
if not in the same workbook unless you set up references
or make if (sic) part of an addin)".

Do you know what he means by "include", or can you tell me
how to set up References in Excel?

Thank you.
 
Kevin Sprinkel said:
Harlan,

Thank you for your response.

The code is in a module in my personal.xls worksheet
loaded on startup. The above link suggests that I will
need to "include the project library (i.e.personal.xls!)
if not in the same workbook unless you set up references
or make if (sic) part of an addin)".

Do you know what he means by "include", or can you tell me
how to set up References in Excel?

Thank you.
Click to expand...

Install it as an add-in instead, open a new workbook, put the code in a
regular module, save the workbook as an add-in TLcoef.xla, let it go to the
default
directory. Now check the name under tools>add-ins.
This way it will be available to all open workbooks
 
Install it as an add-in instead, open a new workbook, put
the code in a
regular module, save the workbook as an add-in TLcoef.xla, let it go to the
default
directory. Now check the name under tools>add-ins.
This way it will be available to all open workbooks

--

Regards,

Peo Sjoblom
Click to expand...

Peo, I've done as you directed but am unable to Save As
a .xla file. I'm using Office XP. Do I need to install
an optional feature, or do you save as an .xla another way?

TIA
Kevin Sprinkel
 
Kevin Sprinkel -
... Linest falls short of the dynamic function I need, and only works for
Click to expand...
linear trendlines. I also require log and quadratic. <

LINEST is dynamic.

LINEST works for linear combinations of variables.

Transform the x data for a log fit.

Use x and x^2 as the x data for a quadratic fit.

- Mike Middleton, www.usfca.edu/~middleton
 
Kevin Sprinkel said:
Peo, I've done as you directed but am unable to Save As
a .xla file. I'm using Office XP. Do I need to install
an optional feature, or do you save as an .xla another way?
Click to expand...

It's the very downmost filetype in the saveas dialog. That's what you tried,
and received an error saying what, if so ?

HTH. Best wishes Harald
 
Kevin Sprinkel -
linear trendlines. I also require log and quadratic. <

LINEST is dynamic.

LINEST works for linear combinations of variables.

Transform the x data for a log fit.

Use x and x^2 as the x data for a quadratic fit.
Click to expand...

The point to using Dave Braden's TLcoef udf is that in versions of Excel prior
to Excel 2003, the fitted coefficients produced by chart trendlines were much
more accurate for perverse observation data than LINEST produced. Are you not
familiar with the poor numeric algorithm used in LINEST in Excel 2002 and prior
versions?
 
Harlan Grove -
The point to using Dave Braden's TLcoef udf is that in versions of Excel
Click to expand...
prior to Excel 2003, the fitted coefficients produced by chart trendlines
were much more accurate for perverse observation data than LINEST produced.
Are you not familiar with the poor numeric algorithm used in LINEST in Excel
2002 and prior versions? <

Maybe a little bit. But I am much more familiar with the idea of looking at
the data and thinking about an appropriate curve-fitting method _before_
using any algorithm for numerical calculations. Thanks.

- Mike Middleton
 
Back
Top