I was wondering if there's any possible way of pulling data for a trendline from a graph and show it in a cell. The two values I'm trying to get are the slope of the line and the R squared value as seen below as y = 2.164ln(x) + 15 and R squared = 1. currently what I have to do is enter the data and the graph is created, then I have to manually type the data
Hi LiamHiggins,
To extract the slope and R-squared value from a trendline in Excel, you can utilize Excel's built-in features. Follow these steps:
Display the equation on the chart: Right-click on the trendline, select "Format Trendline" from the menu, and then enable the "Display Equation on chart" and "Display R-squared value on chart" options.
Extracting data from trendline - Microsoft Community
Utilize the TREND function: Excel's TREND function allows you to calculate y-values based on a trendline for given x-values. This function employs linear regression to determine the result. The syntax is =TREND(known_y’s, known_x’s, new_x’s, const), where known_y’s and known_x’s represent the known x and y data in your data table, new_x’s is an array of data points for which you want to predict the y-value, and const is a binary argument for calculating the y-intercept.
Note that the TREND function acts as a dynamic array formula, automatically spilling the result for the entire array.
www.automateexcel.com
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.
If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)
