Can I do this on Excel?

[Javier Gomez [SBS MVP]]

Hi all!

Not sure if this is the best group to ask this... but here it goes:

I have multiple data points that I want to fit to a line y = mx + b. The
problem is that the y's and x's have different std deviations associated (on
both variables) with each data point. I want to calculate m and b taking
into account that.

Can I do this on Excel? or do you guys/gals have some other suggestion?

Thanks,

 

[Bernie Deitrick]

Javier,

Not sure what you want - you can get an evaluation of the best fit after it
is made. See help on SLOPE, INTERCEPT, LINEST, and STEYX functions.

HTH,
Bernie
MS Excel MVP

 

[Javier Gomez [SBS MVP]]

Thanks Bernie!

Basically LINEST would do what I want... the problem is that I don't think
(or I don't know how) one can enter non-exact numbers to that command.

Let me try to explain-> Normally one would have y1 = 10, y2 = 20, y3=30...
and x1 = 1, x2 = 2, x3=3... and so on. But, what I have is y1 = 10±1, y2 =
20±3, y3 = 30±1... and x1 = 1±0.3, x2 = 20±.2, x3 = 3±0.1... and so on.

I want to best fit that line, calculate the slope and intercept and their
associated error. Is this possible?

 

[Bernie Deitrick]

Javier,

When you have a value like y1 = 10±1 then 10 is the "best value" for y1.

To get an idea of the variations, you could try fitting four cases:

1) set all the values (Xs and Ys) to their minimums
2) them to their maximums
3) set Xs to Min and Ys to Max
4) set Xs to Max and Ys to Min

Then, at least, those four fits would give you some idea for how much your
fit could vary.

For a more statistically satifying method, you could iterate through and
randomly assign each variable's value according to some probablitistic model
(like a normal distrubution)..... but that is not something that Excel can
do natively. Besides, I'm quickly getting in over my head with the stats
part....

HTH,
Bernie
MS Excel MVP

Thanks Bernie!

Basically LINEST would do what I want... the problem is that I don't think
(or I don't know how) one can enter non-exact numbers to that command.

Let me try to explain-> Normally one would have y1 = 10, y2 = 20, y3=30...
and x1 = 1, x2 = 2, x3=3... and so on. But, what I have is y1 = 10±1, y2 =
20±3, y3 = 30±1... and x1 = 1±0.3, x2 = 20±.2, x3 = 3±0.1... and so on.

I want to best fit that line, calculate the slope and intercept and their
associated error. Is this possible?

--
Javier [SBS MVP]

<< SBS ROCKS!!! >>

Javier,

Not sure what you want - you can get an evaluation of the best fit after
it
is made. See help on SLOPE, INTERCEPT, LINEST, and STEYX functions.

HTH,
Bernie
MS Excel MVP

message news:[email protected]... associated
(on

 

[Gaj Vidmar]

The problem has been known to statisticians (and solved in different ways)
for about 130 years.

Look under "Model II Regression".

I Googled for a little and found a really nice introduction (with the
necessary references) at

http://www.plantbio.ohiou.edu/epb/instruct/quantmet/lectures/pdf/Model2.pdf

Ordinary least-squares regression is NOT the right approach. "Reduced major
axis regression" (RMA; also known as geometric mean regression) is the
simplest solution. A site with freeware (executable and in Java) for it
(with a manual) is

http://www.bio.sdsu.edu/pub/andy/RMA.html

As for implementation in Excel, just now I remebered that once I came
through an add-in by M. Sawada (also the author of Rooks Case add-in, which
has to do with spatial auto-correlation), and after some more Googling I
found it at

http://www.uottawa.ca/academic/arts...ownloads/download/sawsoft/modelii/modelii.htm

Regards,

Gaj Vidmar, MSc, biostatistician
Univ. of Ljubljana, Fac. of Medicine, Inst. of biomedical Informatics
www.mf.uni-lj.si/ibmi-english [/biostat-center] [->Software]

 

[Javier Gomez [SBS MVP]]

Thanks Bernie! I will see what I can do... I really suck at "stats" and that
may be part of the problem

--
Javier [SBS MVP]

<< SBS ROCKS!!! >>

Javier,

When you have a value like y1 = 10±1 then 10 is the "best value" for y1.

To get an idea of the variations, you could try fitting four cases:

1) set all the values (Xs and Ys) to their minimums
2) them to their maximums
3) set Xs to Min and Ys to Max
4) set Xs to Max and Ys to Min

Then, at least, those four fits would give you some idea for how much your
fit could vary.

For a more statistically satifying method, you could iterate through and
randomly assign each variable's value according to some probablitistic
model
(like a normal distrubution)..... but that is not something that Excel
can
do natively. Besides, I'm quickly getting in over my head with the stats
part....

HTH,
Bernie
MS Excel MVP

Thanks Bernie!

Basically LINEST would do what I want... the problem is that I don't
think
(or I don't know how) one can enter non-exact numbers to that command.

Let me try to explain-> Normally one would have y1 = 10, y2 = 20,
y3=30...
and x1 = 1, x2 = 2, x3=3... and so on. But, what I have is y1 = 10±1, y2
=
20±3, y3 = 30±1... and x1 = 1±0.3, x2 = 20±.2, x3 = 3±0.1... and so on.

I want to best fit that line, calculate the slope and intercept and their
associated error. Is this possible?

--
Javier [SBS MVP]

<< SBS ROCKS!!! >>

Javier,

Not sure what you want - you can get an evaluation of the best fit
after
it
is made. See help on SLOPE, INTERCEPT, LINEST, and STEYX functions.

HTH,
Bernie
MS Excel MVP

message Hi all!

Not sure if this is the best group to ask this... but here it goes:

I have multiple data points that I want to fit to a line y = mx + b. The
problem is that the y's and x's have different std deviations associated
(on
both variables) with each data point. I want to calculate m and b taking
into account that.

Can I do this on Excel? or do you guys/gals have some other
suggestion?

Thanks,

 

[Javier Gomez [SBS MVP]]

Thanks a lot Gaj! This looks like it is what I need.

--
Javier [SBS MVP]

<< SBS ROCKS!!! >>

The problem has been known to statisticians (and solved in different ways)
for about 130 years.

Look under "Model II Regression".

I Googled for a little and found a really nice introduction (with the
necessary references) at

http://www.plantbio.ohiou.edu/epb/instruct/quantmet/lectures/pdf/Model2.pdf

Ordinary least-squares regression is NOT the right approach. "Reduced
major
axis regression" (RMA; also known as geometric mean regression) is the
simplest solution. A site with freeware (executable and in Java) for it
(with a manual) is

http://www.bio.sdsu.edu/pub/andy/RMA.html

As for implementation in Excel, just now I remebered that once I came
through an add-in by M. Sawada (also the author of Rooks Case add-in,
which
has to do with spatial auto-correlation), and after some more Googling I
found it at

http://www.uottawa.ca/academic/arts...ownloads/download/sawsoft/modelii/modelii.htm

Regards,

Gaj Vidmar, MSc, biostatistician
Univ. of Ljubljana, Fac. of Medicine, Inst. of biomedical Informatics
www.mf.uni-lj.si/ibmi-english [/biostat-center] [->Software]

Thanks Bernie!

Basically LINEST would do what I want... the problem is that I don't
think
(or I don't know how) one can enter non-exact numbers to that command.

Let me try to explain-> Normally one would have y1 = 10, y2 = 20,
y3=30...
and x1 = 1, x2 = 2, x3=3... and so on. But, what I have is y1 = 10±1, y2
=
20±3, y3 = 30±1... and x1 = 1±0.3, x2 = 20±.2, x3 = 3±0.1... and so on.

I want to best fit that line, calculate the slope and intercept and their
associated error. Is this possible?