Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Pulling figures from a graph

Copper Contributor

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 

Excel Graph.PNG

1 Reply

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:

  1. 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

  2. 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)