TITLE: Is there a way to calculate an R^2 value between a two sets of data points on Excel? If not, how may I compare how close they are to each other?
QUESTION [0 upvotes]: If I have a scatter plot of say, 10 values, and I manually generated two functions as the line of best fit for the data points, in attempt to compare which function fits the values of the scatter plot better. How may I do that? Could I use R^2 values?
(In order to plot the two functions using Excel I will be substituting in a range of values as x, and it will be plotted against the generated 'y' data. I know a better way to do this is to use a software like https://www.desmos.com/calculator, but I don't think I can compare how close the data points are to the two function using this software.)
Please see the image below for the data points I am referring to the two functions generated. They are both very close to the data points, what's a way I could figure out which one is a better fit?
Graph of two functions and the set of data points

REPLY [0 votes]: Let's say $(y_i)_{i\in [0,n]}$ is your data set of $n$ points.
Let's have $(f_i)_{i\in [0,n]}$ and $(g_i)_{i\in [0,n]}$ the generated functions points.
A good way to evaluate which one fits better is to take the square root of the sum of the squared difference :
$$F =\sqrt{ \sum_0^n (f_i-y_i)^2 }\text{ and } G = \sqrt{\sum_o^n(g_i-y_i)^2}$$
Then whichever is lower between $F$ and $G$ is the best fitting corresponding function.
Note that here you're using the Euclian distance (built upon Euclidian norm) but there is other distances you could use.
{"set_name": "stack_exchange", "score": 0, "question_id": 2396545}