straight line graph, really straight line..

[Jason]

Hello, i am trying to add a trendline to graph which has 4 points and is
supposed to be producing a straight line (but does not always), so due to
these slight variations, the formula that i get from the trendline is not
always correct. i was wondering if there is a way to get a straight trendline
no matter the values are slightly a bit off, by just ignoring the ones which
dont fall under the "straight line category" ?? for instance, if the 3rd
value is slightly off, then ignoring just the 3rd??

if there is a way to do it through vba, and if you could show me a very
small example of the syntax or the code, or if it could be done in excel
itself, would be a great help as i am hoping to get my course work right..lol

[Maistrye]

Hello, i am trying to add a trendline to graph which has 4 points and is
supposed to be producing a straight line (but does not always), so due to
these slight variations, the formula that i get from the trendline is not
always correct. i was wondering if there is a way to get a straight trendline
no matter the values are slightly a bit off, by just ignoring the ones which
dont fall under the "straight line category" ?? for instance, if the 3rd
value is slightly off, then ignoring just the 3rd??

if there is a way to do it through vba, and if you could show me a very
small example of the syntax or the code, or if it could be done in excel
itself, would be a great help as i am hoping to get my course work right..lol

A fairly simplistic way would be to calculate the slopes between all sets of two points, then take the most common slope. Using this slope, you'll need to find a suitable point on the line to get the equation of the line you're looking for.

General Pseudocode:

For i = 1 to NumberOfPoints
For j = i+1 to NumberOfPoints
x = slope between i and j
Increase the count for slope x - some collection or other should do this for you.
Next j
Next i

Go through your counts of slopes and determine the most common.

Find a point on the line.

Go through all the points to determine which points are on the line.

(Sorry, not 100% familiar with VB to the point where I could type out the actual code for you.)

Scott

[Jason]

Hey, you have given me the idea and the code, thats more than enough for me
to tweak the knobs. if it doesnt work, i will bother you again after
searching the net. , thanks very much..

"Maistrye" wrote:

>
> Jason Wrote:
> > Hello, i am trying to add a trendline to graph which has 4 points and
> > is
> > supposed to be producing a straight line (but does not always), so due
> > to
> > these slight variations, the formula that i get from the trendline is
> > not
> > always correct. i was wondering if there is a way to get a straight
> > trendline
> > no matter the values are slightly a bit off, by just ignoring the ones
> > which
> > dont fall under the "straight line category" ?? for instance, if the
> > 3rd
> > value is slightly off, then ignoring just the 3rd??
> >
> > if there is a way to do it through vba, and if you could show me a
> > very
> > small example of the syntax or the code, or if it could be done in
> > excel
> > itself, would be a great help as i am hoping to get my course work
> > right..lol
>
> A fairly simplistic way would be to calculate the slopes between all
> sets of two points, then take the most common slope. Using this slope,
> you'll need to find a suitable point on the line to get the equation of
> the line you're looking for.
>
> General Pseudocode:
>
> For i = 1 to NumberOfPoints
> For j = i+1 to NumberOfPoints
> x = slope between i and j
> Increase the count for slope x - some collection or other should do
> this for you.
> Next j
> Next i
>
> Go through your counts of slopes and determine the most common.
>
> Find a point on the line.
>
> Go through all the points to determine which points are on the line.
>
> (Sorry, not 100% familiar with VB to the point where I could type out
> the actual code for you.)
>
> Scott
>
>
> --
> Maistrye
> ------------------------------------------------------------------------
> Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
> View this thread: http://www.excelforum.com/showthread...hreadid=563349
>
>