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:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Grand Total[/TD]
[TD]Unit Cost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7,700[/TD]
[TD="align: right"]0.14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21,000[/TD]
[TD="align: right"]5.00[/TD]
[TD]← Outlier[/TD]
[/TR]
[TR]
[TD="align: right"]27,900[/TD]
[TD="align: right"]0.04[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28,000[/TD]
[TD="align: right"]0.04[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29,000[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]58,400[/TD]
[TD="align: right"]0.02[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.