×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Bug in Excel Trend Lines

## Bug in Excel Trend Lines

(OP)
Has anybody encountered a bug in the trend lines which Excel generates from regression? The equation of the trend line did not give me the expected values. I was working on exponential and polynomial type of lines. I had to adjust the co-efficients of the equations to get somewhat better results.

Thanks.

Samiran

### RE: Bug in Excel Trend Lines

Samiran:

The more data points you have, the more the trendline (and its equation) will represent the data you have, especially if your data fits a polonomial expression.

I've had the same experience.  I'll have a bunch of data that the trendline represents really well in the middle, but is in >90% error on either end point of the data.

If you are trying to fit an equation to data so you can calculate "y" by entering "x" into a cell in excel, it may be helpful to split your data into sections and fit trendlines to each section instead of trying to get one equation to fit the entire data range.

Example:

for data of y = 1-200, x = 30-600

Instead of entering all data from y=1 (x=30) to y=200 (x=600), enter y=1 to y=100 in one chart and y=101 to y=200 in another chart and fit trendlines to each graph.

Good luck!

jproj

### RE: Bug in Excel Trend Lines

(OP)
DEAR JPROJ,

The idea you've suggested is actually not bad - to have diff trend line s for diff sets of data. This only shows one should always try to query results before walking away happily with the solution offered by any readymade tool.

Thanks again.

Samiran

### RE: Bug in Excel Trend Lines

Another thing to try is to have the trendline equation displayed with more decimal places. Select the label with the equation written on it and choose "Format Data Labels, Number", then choose a larger number of decimal places. This quite often helps.

Ailsa

### RE: Bug in Excel Trend Lines

Sometimes the accuracy of the trend line is improved if you change the order of the data tables.  In other words, plot X vs Y instead of Y vs X.  I have used this approach to get the curve to fit the data better at the ends of the curve.  It doesn't seem mathematically necessary to manipulate the data this way in order to get the trend line method in EXCEL to work, but it helps.

### RE: Bug in Excel Trend Lines

I have just followed Ailsa's advice and increased the number of decimal places displayed for the equation constants from 1 to 6 (ie from 1.0E5 to 1.000000E5). This totally eliminated a 90% error at the extreme end of the data range. Good? I think so!

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a partâ€™s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!