Scatterplot with two or more lines

BRADX · May 10th, 2002, 09:16 AM

I was wondering if anyone knows how to get a scatterplot and put more than one regression line on it....I want to split it for pos value regression and neg value regression (or line of best fit). If you have any suggestions it would be greatly appreciated.
Thanks a lot,
Have a great day!

Tim Francis-Wright · May 10th, 2002, 02:16 PM

Quote:

On 2002-05-10 08:16, BRADX wrote:
I was wondering if anyone knows how to get a scatterplot and put more than one regression line on it....I want to split it for pos value regression and neg value regression (or line of best fit). If you have any suggestions it would be greatly appreciated.
Thanks a lot,
Have a great day!

You can add trendlines by selecting a point in the data series, then Selecting Chart | Add Trendline...

The easiest way to do a custom trendline is to add a series to the chart, then base the
new trendline on that series.

HTH

Mark W. · May 12th, 2002, 04:38 PM

Plot 2 series instead of 1. For example, if your Y values contained in A2:A8 are...

{1;2;-3;4;7;-5;-8}

Enter the formula, =IF(A2>0,#N/A,A2), into B2 and copy down to B8 to produce negative Y values...

{#N/A;#N/A;-3;#N/A;#N/A;-5;-8}

...and enter the formula, =IF(A2>0,A2,#N/A), into C2 and copy down to C8 to produce positive Y values...

{1;2;#N/A;4;7;#N/A;#N/A}

Chart these as separate chart series...

=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$8,1)
=SERIES(Sheet1!$C$1,,Sheet1!$C$2:$C$8,2)

...and apply a trendline to each series.

[ This Message was edited by: Mark W. on 2002-05-12 15:38 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 10th, 2002, 09:16 AM	#1
BRADX New Member Join Date: May 2002 Posts: 8	I was wondering if anyone knows how to get a scatterplot and put more than one regression line on it....I want to split it for pos value regression and neg value regression (or line of best fit). If you have any suggestions it would be greatly appreciated. Thanks a lot, Have a great day!

May 12th, 2002, 04:38 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Plot 2 series instead of 1. For example, if your Y values contained in A2:A8 are... {1;2;-3;4;7;-5;-8} Enter the formula, =IF(A2>0,#N/A,A2), into B2 and copy down to B8 to produce negative Y values... {#N/A;#N/A;-3;#N/A;#N/A;-5;-8} ...and enter the formula, =IF(A2>0,A2,#N/A), into C2 and copy down to C8 to produce positive Y values... {1;2;#N/A;4;7;#N/A;#N/A} Chart these as separate chart series... =SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$8,1) =SERIES(Sheet1!$C$1,,Sheet1!$C$2:$C$8,2) ...and apply a trendline to each series. [ This Message was edited by: Mark W. on 2002-05-12 15:38 ]