An approximation for non-linear dependencies in spreadsheets
A method for obtaining nonlinear dependencies of type, which based on the use of the function of linearizing the numerical dependencies of spreadsheets. Approximation for the conversion degree of a chemical reaction as a function of 2-3 parameters.
| Рубрика | Экономико-математическое моделирование |
| Вид | статья |
| Язык | английский |
| Дата добавления | 10.08.2018 |
| Размер файла | 235,6 K |
Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже
Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.
Размещено на http://www.allbest.ru
Размещено на http://www.allbest.ru
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. 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
nonlinear numerical spreadsheet approximation
The results of the study were obtained on the basis of a previously designed experiment 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”. 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.
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.
Размещено на Allbest.ru
...Подобные документы
Мета кластерного аналізу: поняття, алгоритм, завдання. Головні особливості процедури Мак-Кіна. Графік середніх значень за трьома кластерами. Метод К-методів, переваги та недоліки використання. Поняття про сіткові алгоритми кластеризації (grid-based).
реферат [238,3 K], добавлен 27.05.2013Experimental details of the chemical transients kinetics and pulsed field desorption mass spectrometry methods. Kinetic measurements with the PFDMS method. Data on the CO hydrogenation over CoCu-based catalysts using CTK. CO hydrogenation reaction.
статья [334,2 K], добавлен 10.05.2011The principles of nonlinear multi-mode coupling. Consider a natural quasi-linear mechanical system with distributed parameters. Parametric approach, the theory of normal forms, according to a method of normal forms. Resonance in multi-frequency systems.
реферат [234,3 K], добавлен 14.02.2010Practical application of linear interpolation, least squares method, the Lagrange interpolation polynomial and cubic spline interpolation. Determination of the integral in the set boundaries in accordance with the rules of the rectangle and trapezoid.
курсовая работа [207,7 K], добавлен 21.09.2010The profit function possesses several important properties that follow directly from its definition. These properties are very useful for analyzing profit-maximizing behavior. Outlining the properties of the profit function important to recognize.
анализ книги [15,2 K], добавлен 19.01.2009Construction of the general algorithm for integration of the linear usual distinctive equation. Creation of the common decision of the differential equation. An example of the decision of linear systems. Definition of components of certain functions.
учебное пособие [2,4 M], добавлен 03.10.2011Ethyl acetate. The existing methods of obtaining the desired product. Technological scheme of EtOAc production. Chemical reactions. Production in industry. Chemical reactions. Methanol as intermediate product. The technology of receiving ethanol.
презентация [628,4 K], добавлен 15.02.2015Function words, they characterization. Determiners as inflected function words employed. Preposition "at": using, phrases, examples from "The White Monkey" (by John Galsworthy). Translation, using, examples in literature preposition "in", "of".
курсовая работа [60,3 K], добавлен 25.11.2011The lines of communication and the basic properties of the fiber optic link. Comparison of characteristics and selection of the desired type of optical cable. The concept of building a modern transmission systems. The main function module SDH networks.
дипломная работа [2,1 M], добавлен 16.08.2016Amortization as a gradual transfer process of fixed assets cost on production price with the purpose of funds accumulation for subsequent repairing. Linear method, disadvantage. Depreciation methods for the sum of years number of the useful term use.
доклад [17,9 K], добавлен 07.05.2013The endocrine system is a control system of ductless glands that secrete hormones within specific organs. Exocrine function of pancreas. Ferments and secretion of digestive juice. Mixed endocrine and exocrine glands are the pancreas, ovaries and testes.
презентация [5,4 M], добавлен 22.11.2015The chiral model of graphene based on the order parameter is suggested in the long-wave approximation, the ideal graphene plane being determined by the kink-like solution. Corrugation of the graphene surface is described in the form of ripple and rings.
статья [211,7 K], добавлен 23.05.2012Chemical reaction. Components of typical DFMC. Micro Fuel Cell Stack for cellular Phone. Developments of micro fuel cell for cellular phones. Proposed design of micro fuel cell. The overall reaction in a DMFC. Construction and main components of DMFC.
реферат [419,1 K], добавлен 21.09.2010Составление транслятора на языке С для перевода кода программы из языка Pascal в код программы на языке Cи. Распознавание и перевод конструкций: for, type, function, integer. Вешняя спецификация, описание, структура, текст программы; распечатка текстов.
курсовая работа [287,8 K], добавлен 24.06.2011Francium is a chemical element that has the symbol Fr and atomic number 87. Soviet chemist D.K. Dobroserdov was the first scientist to claim to have found eka-caesium. Perey's francium analysis. Synthesising francium processing in nuclear reaction.
реферат [604,6 K], добавлен 13.11.2009Shortening of spoken words. Graphical abbreviations and acronyms. Abbreviations as the major type of shortenings. Secondary ways of shortening: sound interchange and sound imitating. Blendening of words. Back formation as a source for shortening of words.
дипломная работа [90,2 K], добавлен 10.07.2009Lines of communication and the properties of the fiber optic link. Selection of the type of optical cable. The choice of construction method, the route for laying fiber-optic. Calculation of the required number of channels. Digital transmission systems.
дипломная работа [1,8 M], добавлен 09.08.2016Study of method of determining the amount of osteocyte lacunar and estimation of specific numerical closeness of lacunes by a three-dimensional impartial expecting method at the analysis of anisotropy of types of the vascular ductings of human bone.
реферат [8,6 K], добавлен 01.12.2010Oxygen carriers in CLC process. State of art. General oxygen carriers characteristics. Dry impregnation method. Fluidized Beds. Advantages and disadvantages of the Fluidized-Bed Reactor. Gamma alumina. Preparing of solution. Impregnation calculations.
курсовая работа [5,9 M], добавлен 02.12.2013Niobium or columbium is the chemical element with the symbol Nb and the atomic number 4. Physical and chemical properties Niobium. Niobium is in many ways similar to its predecessors in group 5. Application of the given chemical element in the industry.
реферат [51,0 K], добавлен 09.01.2012
