Results 1 to 5 of 5

Thread: sorting multiple trendline equations using VBA

  1. #1
    Neophyte timfrey1's Avatar
    Join Date
    Feb 2021
    Posts
    2
    Articles
    0
    Excel Version
    Microsoft Excel for Office 360

    sorting multiple trendline equations using VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    (My first post)
    In VBA I created a chart with 2 sets of data and added trendlines with automatic display of the equations and R-square values. However VBA always put the equations and R-square on top of each other and When I manually separate them I am not sure which trendline equation goes with which set of data, especially if the 2 sets of data are fairly similar. Subtle equation coefficients are critical to my analysis. Is there anyway I can move the first trendline equation before the second trendline equation is put on the graph? Thanks in advance for any reply. Stay safe!

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,936
    Articles
    0
    Excel Version
    365
    Attach a small workbook with your existing code and charts - it may just be a tweak - that would:
    • save us guessing wrongly what's in your file
    • give us something to experiment with
    • make it more likely you get the most appropriate response

  3. #3
    Neophyte timfrey1's Avatar
    Join Date
    Feb 2021
    Posts
    2
    Articles
    0
    Excel Version
    Microsoft Excel for Office 360

    current snip of code

    Quote Originally Posted by p45cal View Post
    Attach a small workbook with your existing code and charts - it may just be a tweak - that would:
    • save us guessing wrongly what's in your file
    • give us something to experiment with
    • make it more likely you get the most appropriate response
    Below is a snippet of the code. I'm not proficient. I could place the chart where I want it but cant figure out how to place the trendline equation and R-square values. Since I plot 2 sets of data, I need 2 trendline equations. The code just places one on top of the other in the chart. Thanks.


    ' add the chart
    Set myChtObj = ActiveSheet.ChartObjects.Add _
    (Left:=350, Width:=600, Top:=60, Height:=300)
    With myChtObj.Chart


    ' make an XY chart
    .ChartType = xlXYScatterLines
    .HasTitle = True
    .ChartTitle.Caption = Range("$B$1")
    .Axes(xlCategory, xlPrimary).MinimumScaleIsAuto = False
    .Axes(xlCategory, xlPrimary).MaximumScaleIsAuto = False
    .Axes(xlValue, xlPrimary).MinimumScaleIsAuto = True
    .Axes(xlValue, xlPrimary).MaximumScaleIsAuto = True
    .Axes(xlCategory, xlPrimary).MaximumScale = SampleLen
    .Axes(xlCategory, xlPrimary).MinimumScale = 0

    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Caption = "Length of Shade (in.)"
    .Axes(xlValue, xlPrimary).AxisTitle.Caption = "Force (grams)"


    With .SeriesCollection.NewSeries
    .Values = rngChtXVal.Offset(0, 1).Resize([StepTotal], 1)
    .XValues = rngChtXVal.Resize([StepTotal], 1)
    .Name = "Extension"
    .Trendlines.Add Type:=xlPower, Name:="Ext Trend"


    End With
    With .SeriesCollection.NewSeries
    .Values = rngChtXVal2.Offset([StepTotal], 1).Resize([StepTotal], 1)
    .XValues = rngChtXVal2.Offset([StepTotal], 0).Resize([StepTotal], 1)
    .Name = "Retraction"
    .Trendlines.Add Type:=xlPower, Name:="Ret Trend"

    End With

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,936
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by p45cal View Post
    Attach a small workbook
    Come on, please. No one's going to start putting together dummy data and then a chart in the hope that they get it right. You'll get the best advice/solution for your problem if you attach an Excel workbook.
    If the stuff is sensitive, alter the data so it no longer is, while retaining realism.

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,936
    Articles
    0
    Excel Version
    365
    By the way,
    Quote Originally Posted by timfrey1 View Post
    Subtle equation coefficients are critical to my analysis.
    are you intending to extract these values from the chart trendline label? There is a way to get these data easily and accurately without using a trendline label (using LINEST), also without even using a chart.
    Last edited by p45cal; 2021-02-16 at 09:36 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •