Optimizing Performance

  It may be possible to optimize the series of challenges that must be passed before an action is to be performed by taking into account the length of time, T, it takes to process a particular test and the relative frequency, p, of its occurrence. The time it would take to eliminate case i if tests are done serially would be something like,

Σⁱ_k=1 p_kT_k 

so one might try to do the more frequent and easier tests first. Operations research has produced procedures like PERT for planning projects.

  This might be a consideration for those interested in reducing government inefficiency. Laws could be designed by the Legislatures with consideration for the needs of the Executive and Judicial branches as well as the public in general by simplifying the decisions required. Executive orders could handle the way in which requests of the Executive branch are processed by separating rare difficult requests from the more common simple ones. The same may be true for the way in which the courts hear their petitions.

The actions of all branches of government should be subject to considerations of "good conduct."


Supplemental (Mar 1): The expected processing time for a series of tests that individually take time T_k would be,

T_avg=Σⁿ_k=1 p_kT'_k where T'_k= Σ^k_i=1 T_i  or equivalently,

T_avg=Σⁿ_k=1 μ_kT_k where μ_k= Σⁿ_i=k p_i.

Note that μ₁=1 so T_avg is always greater than the time it takes to do the first test and why we would prefer to do the shorter tests first.

Running The Gauntlet

  Programming can be rather grueling at times. Right now I'm feeling like I've been doing a mind meld with a Vulcan again. Low grade headache. Stiff neck. Fatigued. Finding it difficult to focus. It's like I've been doing a mental version of running the gauntlet. But I've improved the range of the imround macro with a rewrite. The heart of the macro now is an IsComplex function designed to recognize the complex number format. It follows the design strategy of dumping a task if it doesn't recognize a format. Presumably, the user will handle the more complex cases. The numbers [N] are signed double precision and range in magnitude from 2.22507385850721E-308 to 1.79769313486232E+308.

Knowing the number format makes it easier to make simple corrections if the number is written with the i preceding the second number. The imround macro can now round large numbers and numbers with exponentials. Here's part of a test that it passed.

The second column contains the simplified complex numbers. The IsComplex function thinks I5 looks like a complex number. It appears to be missing a sense of context.

Supplemental (Feb 28): Feeling better after a little rest. Running the gamut might be a better expression for including all the desired syntaxes to be acted on.

Designing Smart Macros

  Like a scribe in ancient Egypt a programmer has to be prepared to handle seemingly impossible tasks. One can be ultra cautious and try to handle all possible exceptions but the price of that is a reduction in efficiency resulting in increased computing times. However not catching an error can cause processing to be halted and everything done prior to that is lost. So a compromise might be to run a simple test that will handle most cases and use a more sophisticated procedure to handle rarer cases.

  In Excel the complex numbers are stored as strings. An imround macro that would round a complex number to a fixed number of decimal places could be programmed to check to first see if the selected cell contained a string and abort operation is it doesn't. One could then check to see if the string is of the form x+yi where x and y are decimal numbers. But complex numbers can also be written in scientific or engineering format where exponentials occur. Numbers like 1/2+2/3i are also complex numbers but this is more like a formula and we can exclude it from the range of possibilities that the macro has to act on. I was able to create a smarter version of my original imround macro that could handle simple complex numbers and ignore anything else that might be contained in a cell. It's a fail safe strategy.

Note that the result of a rounding operation may not necessarily be a complex number. Above 1.+0.i is rounded to 1. It might be wise for computer programs to be designed in such a way that they have a known area of competence and avoid acting outside it.

Some Warnings about Using Macros in Excel

  Macros are useful utilities in Excel but they present some additional difficulties which one should be on the watch for. I used a macro to record the steps involved in reducing the displayed precision of a complex number. The selected cell was predetermined in the macro and activated by using the Ctrl-r shortcut key. Here's a screen capture of part of the page before activating the macro. Notice the Undo button is available for use and a formula is present in the formula bar.

After the macro is activated there are changes on the worksheet. The Undo button is no longer available and the formula has been altered.

 It's best to save a worksheet before using a macro.

  The recorded macro only acted on the original cell and did not depend on which cell was selected when the macro was activated so I rewrote it in VBA to make it more useful by allowing changes in the desired number of decimal places rounded to and an arbitrary selection of the cell to be acted on. Macros like the one above might be useful for preparing online versions of a worksheet which pass just the results of a calculation and not the details about the procedures used to arrive at the results if it is desired that they remain privileged.

  When rewriting macros one should take care to make sure that certain assumptions about the cell(s) to be acted on by the macro are fulfilled and that the action is aborted if these assumptions are not met. That's a given in object-oriented programming.

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.

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=x³+3x²+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.

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.

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.

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+B₂=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.

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 u³ 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.