Linking Chart Text to Cell

[VBA learner ITG]

Good morning Experts,

I was wondering is there a formula or VBA solution to my issue.

If i have a chart with Y and X values on the graph, is there a formula or way of automatically populating a cell with the values.

Example: D29 should be the Y value from chart.
Example: D30 should be the X value from chart.

Sample worksheet below.

[FONT=&quot]Download link [/FONT]
https://zackcarter.wetransfer.com/downloads/0ce4c784a5803247a1c75cd918f386b020171102113508/b840823ddfa3bb18e1472dff76b961aa20171102113508/c8161a

 

[VBA learner ITG]

I know the solution to my issue is a Linest formula.

I have tried so many variations i am now lost in Linest.

[h=3]Linear Trendline[/h]Equation: y = m * x + b
m: =SLOPE(y,x)
b: =INTERCEPT(y,x)
[h=3]Logarithmic Trendline[/h]Equation: y = (c * LN(x)) + b
c: =INDEX(LINEST(y,LN(x)),1)
b: =INDEX(LINEST(y,LN(x)),1,2)
[h=3]Power Trendline[/h]Equation: y=c*x^b
c: =EXP(INDEX(LINEST(LN,LN(x),,),1,2))
b: =INDEX(LINEST(LN,LN(x),,),1)
[h=3]Exponential Trendline[/h]Equation: y = c *e ^(b * x)
c: =EXP(INDEX(LINEST(LN,x),1,2))
b: =INDEX(LINEST(LN,x),1)
[h=3]2nd Order Polynomial Trendline[/h]Equation: y = (c2 * x^2) + (c1 * x ^1) + b
c2: =INDEX(LINEST(y,x^{1,2}),1)
C1: =INDEX(LINEST(y,x^{1,2}),1,2)
b = =INDEX(LINEST(y,x^{1,2}),1,3)
[h=3]3rd Order Polynomial Trendline[/h]Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b
c3: =INDEX(LINEST(y,x^{1,2,3}),1)
c2: =INDEX(LINEST(y,x^{1,2,3}),1,2)
C1: =INDEX(LINEST(y,x^{1,2,3}),1,3)
b: =INDEX(LINEST(y,x^{1,2,3}),1,4)

 

[thisoldman]

This is the data you show:

Grand Total	Unit Cost
7,700	0.14
20,000	0.02
20,000	0.02
20,000	0.02
20,000	0.02
20,000	0.02
20,000	0.02
20,000	0.02
20,000	0.02
20,000	0.02
20,000	0.02
20,000	0.02
21,000	5.00	← Outlier
27,900	0.04
28,000	0.04
29,000	0.02
58,400	0.02

<tbody>
</tbody>

You can generate points that fit a trendline using LINEST, yes. But first, you have to determine how well the trendline fits the data.

As you've learned, it's easy to add a trendlines to a chart and display the equation for the trendline. It's just as easy to get an idea of how well the trendline fits the data—have the chart display the R² value. R² will be a value between zero and one. The closer the value is to one, the better the fit to the data. If I remember correctly, R² is the "coefficient of determination." I just call it "are-squared." It's a measure of how far your actual data points are from the trendline.

None of the canned Excel trendlines give a good fit to your data, even when the obvious outlier is tossed out.

Yes, you could calculate a y-value for a known x using the values from LINEST. But the data you showed doesn't produce a trendline where the effort will produce anything worthwhile.

In an analytical chemistry lab, I usually wouldn't calculate y-values from a trendline equation unless the R² was above 0.9 or 0.95. For production plant process data, I've sometimes been happy with an R² of 0.75. The data you posted just doesn't produce a meaningful trendline.