Search code examples
excelvbachartstrendline

excel graphing tool or vba solution

i have a histogram in Excel. I added a trendline to this histogram which looks like a normal curve.

enter image description here
(source: sourceforge.net)

(it is the image all the way to the left)

I would like to know how I can identify a datapoint on the trendline

Is there a tool that does this? The solution can be in VBA if it needs to be.

Solution

  • If you format the trendline, you can display the equation for the trendline on the chart. You can then copy the equation into a cell and for any value of x you input, you can calculate the y value.

    EDIT

    I used this VBA code to programatically retrieve the equation from the trendline. Maybe it will help.

    Sub ChartStuff()

    Dim cht As Chart
    Set cht = Charts("Chart1")

    Dim ser As Series
    Set ser = cht.SeriesCollection(1)

    Dim tnd As Trendline
    Set tnd = ser.Trendlines(1)

    MsgBox (tnd.DataLabel.Caption)

End Sub

    EDIT

    To move the chart so that it is its own sheet, not an object on a worksheet, right-click on the chart object, choose the 'Move Chart' option, and select the 'New Sheet' radio button. This will create a new tab in your workbook that is only the chart. You can then refer to it by the name on the tab.

    enter image description here