Hyperbolic Curve Fitting in Excel

  1. A hyperbolic function has the form $$ y = \frac{m \times x}{k+x} $$

Our goal is to find $m$ and $k$ that best fit given data. To find $m$ and $k$, we need to:

First, linearize the hyperbolic equation, that is, we need to generate from it a function of the form$ y = m \times x + b$. This can be achieved by (i) raising each side of the equation to the power of $-1$ and (ii) with a little bit of rearranging, we can get the hyberbolic function to look like a linear function,

$$ \frac{1}{y}=\frac{k}{m} \times \frac{1}{x}+\frac{1}{m} $$

Second, we can use the SLOPE and INTERCEPT functions from Excel to find $\frac{k}{m}$ and $\frac{1}{m}$, i.e., the slope and the intercept of the linearized version of the hyperbolic function.

Finally, with a little bit of algebra we can find $m$ and $k$ which give us a model (or a equaiton) that best fits the given data. See the following example

In [10]:
from IPython.display import IFrame
IFrame(src='https://docs.google.com/spreadsheets/d/e/2PACX-1vTd_r2OzHJ1WbcgSuatqb0GCaoD8XKiSwmYr_3wDwfd2rvns7tW8aleAKh0XRyUo6FTVPpI8wZmwYWZ/pubhtml', width=860, height=1100)
Out[10]:
In [ ]: