CODEWITHSUNDEEP
excellinear-regressiontrendline
Bukaida
Bukaida

Reputation: 235

How to show two regression trend lines in one chart using excel 365

I want to plot two trend lines, one from 2-3 cm and the other from 3-4 centimeter like the attached picture. How can I do it using Excel 365?

Sample

My output is coming like this My output

Upvotes: 1

Views: 2198

Answers (2)

Bryan Rock
Bryan Rock

Reputation: 632

Here's a solution without the need to split your dataset.

enter image description here

The chart is a XY scatterplot, with 7 series (Vol-1, Vol-2, Vol-3, Vol-4, Vol-5, Trend1, and Trend2)

The Trend1 series is a bit weird, but basically in the Series X Values box, you select the Dist column (A2:A4 only) 5 times separated by a comma:

=('Temp Sheet'!$A$2:$A$4,'Temp Sheet'!$A$2:$A$4,'Temp Sheet'!$A$2:$A$4,'Temp Sheet'!$A$2:$A$4,'Temp Sheet'!$A$2:$A$4)

Then in the Series y-value box, you select the appropriate rows in each of the Vol columns:

=('Temp Sheet'!$B$2:$B$4,'Temp Sheet'!$C$2:$C$4,'Temp Sheet'!$D$2:$D$4,'Temp Sheet'!$E$2:$E$4,'Temp Sheet'!$F$2:$F$4)

So Trend1 looks like this:

enter image description here

Repeat the same thing for Trend2, but with rows 4 through 6.

Next, add a trend line for Trend1 and Trend2.

enter image description here

Finally, remove the markers for Trend1 and Trend2 so the only datapoints visible are Vol1 through Vol5.

From there you may choose to add axis titles, set axis limits, etc.

Upvotes: 2

Solver Max
Solver Max

Reputation: 391

Split your data into two series (2-3 and 3-4), then add a trendline to each series.

Upvotes: 0

Related Questions