Sunday, February 19, 2017

User Defined Functions Can Simplify a Calculation

  It's rather difficult to do a curve fit in Excel due to the complexity of the formulas. The following example shows how user defined functions can simplify a procedure that finds a least squares fit to set of four points on the curve y=x3+3x2+3x+1. The point x0=-0.9, y0=0.004 gives the medians for the data and the fit coefficients, fit(_x,_y), of a cubic polynomial are for deviations from this point.

Another user function yfit is used to calculate points on the fitted curve for a particular x value.

The fill and drag procedure was used to fill in the table. A plot of the data is useful to compare the results.

Wednesday, February 15, 2017

Creating User Defined Functions in Excel

  It's possible to extend the functionality of an Excel "mathcad" by creating user defined functions. One can do this using Visual Basic for Applications (VBA) and then use the newly defined function as one would the standard functions available for use with Excel. I was able to create a function, cubic_eqn(coefficients,x) that would evaluate a cubic equation given its coefficients and a given x value.

The user defined function can be accessed using Insert Function.

Don't forget to save your VBA project.

Tuesday, February 7, 2017

Computing Cubic Curves Using a Recursion Formula

  One can express the finite differences in the previous post with the values of y' to obtain a recursion formula that simplifies the calculation of the table.

Again one does an initial sample calculation using the recursion formula and then selects it and drags down to repeat the calculation for successive entries.

Computer programmers use recursion formulas like this to reduce the number of calculations needed to do a given computation. One does not need to know the coefficients of the cubic equation to calculate the entire curve; the first four points are sufficient.

Computing Cubic Curves Using Finite Differences

  The following tables compares the computation of a portion of a cubic curve by two different methods, the first using a cubic equation, y, and the second using finite differences, y'. The results are indistinguishable.

The first three columns are generated by setting up the first row and then chaining the calculations of the remaining rows to it using the selection and drag procedure.

To create an initial table of finite differences one calculates the first four values of y' using the cubic equation and calculates the differences shown in blue. For a cubic curve the third differences are all the same so one can use drag and fill replicate the entries in the last column. The differences to the left are found doing simple sums since each entry is equal to the previous entry plus its difference.

Once we have the difference table the values of y' are calculated by the sum of a previous value plus three differences.

The finite difference method is preferable from a calculation point of view since it doesn't involve multiplications which are more time consuming but only simple sums are required. The accuracy of the result does not depend on the size of the step.

Monday, February 6, 2017

Plotting Curves in Excel

  One has to create a table containing values of the coordinates x and y to plot a curve in Excel. This plot used smoothing to improve its appearance.

One can right click the chart and use "Select Data..." in the drop down menu to specify the horizontal and vertical data for the curve's series. One can also click on the axes and series to "format" the appearance of the chart. The only real solution for the cubic equation is x=-1.22249 as previously shown.

Supplemental (Feb 6): The axes are incorrectly labeled on the chart. horizontal is x and vertical is y.

Using Excel 2016 as a Mathcad

  Although Excel 2016 isn't optimized to function as a mathcad it has a limited capability for doing so. One can use it to take a look at the auxiliary parameters, u and v, used to solve the cubic equation. Here is a check to see that the values satisfy the equation 3uv+B2=0.

An Excel chart can be used to give a visual confirmation of the relative position of these parameters.

The auxiliary equation allowed us to find the cube of u and the formula above relating u and v shows they are simply related.  The Excel chart allows one to plot a number of datasets at the same time. The circles in the plot above were generated by computing a table of x,y values and using the columns of data to specify a "series" for the chart. There's a video that explains how to create charts using spreadsheet data but doing plots like this involves a lot of trial and error.

Saturday, February 4, 2017

Solving a Cubic Equation With Excel 2016

  Although complex numbers appear to be surreal and seem to involve something like Zeno's paradoxes they are quite useful. One can use Word 2016 to illustrate how one can use Cardan's method to solve a more general cubic equation. We first note that the coefficients of a cubic equation can be expressed in terms of its roots. We don't need a general coefficient for the cubic term since it can be eliminated by division.

One can also show that the coefficients are related to the sum of powers of the roots but this doesn't appear to be particularly useful.

One can use the mean of the roots to eliminate the coefficient, A, of the squared term in the equation by making the following substitution.

Then Cardan's method can be used to find an auxiliary equation for the solution for y.

One can solve this equation for u3 using the quadratic formula and find the cube roots to obtain solutions for u. If either B' or C' are zero the equation for u can be simplified further but we will assume that they are not. This method can be used in Excel 2016 employing the engineering functions to find the roots of a cubic equation.

After a little difficulty I was able to get an Excel scatter chart to plot these roots on a complex plane.

In the plot above the point z is equidistant from the three roots. Such a point only exists if the roots do not have a common line going through them. It can be determined as follows using the method of least squares. The asterisks indicate complex conjugates.

The expression for u above can be used to find z.