Excell scatterplot

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:35 PM GMT

Question

ive made a scatterplot using excell 2007, but i need to add a line. I know how to add a linear regression line, but i need to add one with my own slope and y intercept. I can alter the linear regression line, but i can't get the slope i want since it is required to go through the means. How do i do this?

Answer

Let's say your X-data is in column A and Y-data in B. Put slope in D2 and Y-intercept in E2 (arbitrary locations). In G2 put =MIN(A:A) and G3 put =MAX(A:A) in H2 put =$D$2*G2+$E$2 and fill down to H3 (y=mx*b) Select a data point from the first series so you see the SERIES formula, copy that by dragging across the formula & using ctrl/C. Click on the chart (not a series) and paste, but change the new SERIES formula to be: =SERIES(,Sheet1!$G$2:$G$3,Sheet1!$H$2:$H$3,2) Then while one of the 2 points of this series is selected, press ctrl/1, select line style & give it a width of 1 (or more), or change the line color to be a solid line, and you have it. If this is unclear, send me your email address

Advertisement

©2017 eLuminary LLC. All rights reserved.