Doing Running Fits For A Set of Data

  Excel can be a little sparse with information at times and the online help more often than not leaves you still asking. Bill Gates' standard response on how to do something is to read a tutorial. So if you need help with user functions try Mansfield, Mastering VBA for Microsoft Office 2016. A knowledgew of Visual Basic itself is also helpful.

I've been exploring what one can do with user functions and a common task for a "mathcad" is interpolating data. In the following example the data for the length of a curve is interpolation is used to obtain uniform steps along the curve.

The function used again was y=x³+3x²+3x+1 so dy/dx=3x²+6x+3 and ds/dx=√(1+(dy/dx)²). The values of s were found by treating ds/dx as differences and doing a running sum. Since s does not vary uniformly with x a table with uniform steps for s was calculated. The sets of coefficients on the right were found by fitting chunks of x and y and were used to obtain the unknown x values. This is the resulting plot.

The user functions are similar to macros and may present a problem with online versions of a worksheet. Excel allows the user to import data from a file so one might save the results of a calculation to a text file and then create a version of the page using imported data.

Supplemental (Feb 21): Technically, in the above example, the values of ds/dx should be multiplied by a Δx to get an estimate of Δs but the x values were uniformly spaced so the Δs values are just a rescaled version of ds/dx and can be ignored.