Total Least Squares Estimation in Microsoft Excel, Generating Trendlines for Statistical Distributions in Excel, and macro-free Excel Solvers for the Roots of Cubic Equations and the Extreme Point of Quartics

 A diagram showing how a parabola may be used to approximate a fitted curve, in order to approximate Total Least Squares estimation

In the summer of 2020, during the Coronavirus lockdown, I was frustrated by what I deemed to be poor statistical modelling of the spread of the virus. Lacking complex software of my own, I set about developing tools for Excel that could be used to model probability distributions. I returned to the project in 2021, developing the following five-part report, in PDFs and Excel templates.

By using the Richards worksheet in the Excel workbook for Part 5, you may model the spread of the coronavirus (as was my ultimate intent when I wrote these pieces over the spring of 2020). The other parts of the project include templates to fit a parabola to any three points and macro-free Excel solvers for cubic equations and for the stationary points of quartic functions.

I consider this project a work in progress. In mid-2021, RWD Nickalls was kind enough to offer me some suggested improvements; I hope to incorporate these in 2022.


  1. Fitting Probability Distributions with Microsoft Excel
    1. This includes the Normal, Logistic, Gumbel, Shifted Gompertz, Generalized Logistic (Richards) & Skew Normal distributions. 
    2. As was topical in 2020, the Richards Distribution can be used to model the spread of the coronavirus.
    3. If you wish to model any of these distributions in Excel, you may use this Excel workbook, which I have adapted from Dave.Kimble@Liz on the OzGrid forums.
  2. Fitting a Parabola to Three Points
    1.  Any three points can be fitted to a parabola, using this Excel workbook.
  3. Features of Cubic Equations
    1. This piece is based on the approach of RWD Nickalls.
    2. This piece includes descriptions of the inflection point, stationary points, and the roots (real and imaginary).
    3. The roots of any cubic equation (real and imaginary) can be found using this Excel workbook. Unlike many other online versions, this one involves no macros.
  4. Finding the Extreme Point of a Quartic Function
    1. This piece builds off Part 3 on cubic equations.
    2. The extreme point (and other stationary points) of any quartic function can be found using this same Excel workbook, which involves no macros.
  5. Approximating Total Least Squares Estimation
    1. This piece describes several methods of varying complexity that can be used to approximate Total Least Squares estimation (TLS).
    2. It also summarizes the key points from Parts 2, 3, and 4.
    3. All the TLS approaches can be used in Microsoft Excel, by using this Excel workbook. Note that this workbook incorporates as examples several of the distributions described in Part 1.