Draw connector line between independent variable and dependent variable

gailb
2StarLounger
Posts: 191
Joined: 09 May 2020, 14:00

Draw connector line between independent variable and dependent variable

Post by gailb »

I have a simple scatter plot that plots the independent variable and dependent variable. Is there a way to draw a connector line between the x,y plot and the predicted y? As an example, I manually drew some arrows to illustrate what I'm trying to achieve.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Draw connector line between independent variable and dependent variable

Post by HansV »

I hope someone else can help you with this, I don't have the slightest idea.
Regards,
Hans

p45cal
StarLounger
Posts: 78
Joined: 11 Jun 2012, 20:37

Re: Draw connector line between independent variable and dependent variable

Post by p45cal »

I'm looking at it…

gailb
2StarLounger
Posts: 191
Joined: 09 May 2020, 14:00

Re: Draw connector line between independent variable and dependent variable

Post by gailb »

Thank you

p45cal
StarLounger
Posts: 78
Joined: 11 Jun 2012, 20:37

Re: Draw connector line between independent variable and dependent variable

Post by p45cal »

This is more awkward than it seems; I ended up creating a new series for each pair of points which is very fiddly, so further created a macro (blah) to do it.
Before running this macro, you're meant to select the chart it works on. It further assumes:
  • there are at least 2 series already in existence on the chart
  • that the first 2 series are the ones you want the arrows between
  • that they have the same number of points
  • and a whole lot more that are already satisfied in your sample workbook
Given all that, put the macro in a standard code-module, select the chart and run it.

In the attached, button on the sheet (select the chart first). I've moved your arrows away from where the new ones will be.
Don't click the button multiple times for a chart, you'll just end up with a chart with hundreds of series.

The macro:

Code: Select all

Sub blah()
With ActiveChart
  Set sc = .FullSeriesCollection
  xvals1 = sc(1).XValues
  xvals2 = sc(2).XValues
  yvals1 = sc(1).Values
  yvals2 = sc(2).Values
  For ix = 1 To UBound(xvals1)
    Set ns = .SeriesCollection.NewSeries
    With ns
      .XValues = Array(xvals1(ix), xvals2(ix))
      .Values = Array(yvals1(ix), yvals2(ix))
      With .Format.Line
        .EndArrowheadStyle = msoArrowheadTriangle
        .Weight = 1
      End With
    End With
  Next ix
End With
End Sub
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Draw connector line between independent variable and dependent variable

Post by HansV »

That's a clever solution! :thumbup:
Regards,
Hans

gailb
2StarLounger
Posts: 191
Joined: 09 May 2020, 14:00

Re: Draw connector line between independent variable and dependent variable

Post by gailb »

Thank you so much for your time. This is working great.