AN APPROXIMATION FOR NON-LINEAR DEPENDENCIES IN SPREADSHEETS

In various scientific studies, there are typical components that are characteristic for different stages. At the stage of processing the experimental data, such steps are the approximation of the experimental data obtained, an assessment of the adequacy of the model obtained and its use to determine the optimal or rational parameters of the process under study. In this article, the first stage is considered and a way of using the linearization function to obtain any nonlinear dependencies is described.

A common use of the LINEST function

To solve this problem, the built-in function “ЛИНЕЙН” (LINEST) is used, the possibilities of which are given in Excel’s help or in books [1]. Usually, this function is used to obtain linear dependencies from one or more parameters. To use it for nonlinear dependencies, such initial data are preliminarily linearized by logarithm. An example in chemistry is the processing of kinetic data to obtain values of the order of reaction, rate constants, and activation energy.

However, not always linearization using logarithm leads to the desired result and therefore the standard use of the LINEST function is not applicable. The solution is the “deception” of this function, in which a non-linear relationship is organized on a sheet of the spreadsheet in the required format. It should be noted that the choice of the initial non-linear dependence (power series, exponential or any other) is made by the user. That is, the accuracy of the obtained approximation will depend not on the LINEST function, but on the chosen nonlinear dependence format.

Let’s consider the given approach on a concrete example of approximation of dependence of function on two and three parameters: degree of oxidation in a chemical reaction from the concentration of a reagent and time of contact. We first estimate the accuracy of a simple linear approximation for these data (fig. 1).

Figure 1. Data processing with LINEST function

The results of the study were obtained on the basis of a previously designed experiment [2] on the effect of temperature and concentration on the degree of conversion. Naturally, any set of data can be approximated provided that such data is sufficient to determine the unknown regression coefficients.

The results of a simple linear approximation (fig. 1) indicate a complete coincidence of calculations based on natural (regression coefficients bi) and coded (regression coefficients ai) parameters. Relative deviations are up to 3 percent in the main. However, the 2nd point with a slight absolute deviation gives 27% of the relative deviation.

“Cheating” the Function

The basis of the proposed approach is the deception of the LINEST function, in which a non-linear dependency is formatted for use with a function that processes linear dependencies. Figure 2 shows the results of calculations for a nonlinear second-order model with natural parameters. The level of relative error is 0.01-0.04%.

Figure 2. “Cheating”  the LINEST function

It should be noted that the LINEST function returns the regression coefficients in the reverse order. To obtain these coefficients, one need to use the procedure “select, F2, Ctrl-Shift-Enter” [3]. Interestingly, the Calc program in LibreOffice displays the results of the function with several results without additional user actions (fig. 3)

Figure 3. “Cheating”  the LINEST function in Libre Office without ”select, F2, Ctrl-Shift-Enter” action

Application to a function of three variables

Let us consider the results of approximation for a variant with three parameters. The same trend with respect to the accuracy of simple linear (fig. 4) and non-linear approximations (fig. 5) is retained for this variant.

Figure 4 - The usual application of the function

Figure 5. The unusual application of the function

It’s obvious that the inaccuracy of simple approximation increases and the accuracy of nonlinear approximation remains at a very good level.

Conclusions

The presented approach to the treatment of experimental data has been used for many years at the Authors’ Department in KPI. In addition to solving actual problems in the processing of experimental data, this approach is of pedagogical interest as an example of a non-standard solution based on the well-known. It should be noted that neither the Excel Help nor the excellent book by John Walkenbach considers such application of the LINEST function for data processing.

1. John Walkenbach. Excel 2003 Bible. Wiley, 2003. ISBN 9780764539671
2. DOE. URL:  https://en.wikipedia.org/wiki/Design_of_experiments
3. Kontsevoy S., Kontsevoy A. Functional calculation of process flows’ parameters in spreadsheets // Modern scientific researches and innovations. 2018. № 5 [Electronic journal]. URL:  http://web.snauka.ru/en/issues/2018/05/86532

All articles of author «Концевой Сергей Андреевич»