straight line graph, really straight line..
 

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

straight line graph, really straight line..
 

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

straight line graph, really straight line..
 

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