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

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

Answer 1

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.