Calculating Formula from Plot Points

Question

Ok, so it's been a very long time since I've done anything like this. I've got a big table of data to enable you to look up a certain $X$ value (bodyweight) and get a $Y$ value (co-efficient for weightlifting). I want to condense this down into a formula, no matter how complex, that I can put into an Excel spreadsheet to track some training. I've grabbed a few data points from the table, that look something like this:

X    Y

40   1.32435
50   1.0099
60   0.83285
70   0.72625
80   0.6578
90   0.61185
100  0.5813
110  0.5625
120  0.55095
130  0.54032
140  0.53117
150  0.52318
160  0.51588
170  0.509075
180  0.5023
190  0.49582
200  0.4903
210  0.48581
220  0.48231
230  0.47891 

Any help is greatly appreciated.

Answer 1

You could create a linear regression file right in Excel! (These instructions are for Excel 2013 but should be sort of similar in older versions)

• Input your data into two separate columns (x and y)

• Then click and drag from the top most left data down to the bottom most right data

• Insert a scatter plot using those points

• Right click on one of the points in the graph and choose "Add Trend Line"

• A trend line will be drawn on the graph according to you data

• If you double click on the line you can change it to an exponential, linear, logarithmic, polynomial, or power equation

• In the same format trendline menu, towards the bottom, you can select to display the equation on the chart

Hopefully this is what you are looking for!

Answer 2

Taking a look at your data, I guessed that maybe a function of the form $f(x)=a/x^p+b$ might work. If we then parameters $a$, $b$, and $p$ to minimize $$\sum_{(x,y)\in \text{data}} (f(x)-y)^2,$$ we find $f(x)=\frac{2106.91}{x^{2.11436}} + 0.463718$. The sum of the squared errors is $0.000480789$, which seems pretty good. The graph of $f$ with the data looks like