NUMERICAL METHODS FOR SOLVING ALGEBRAIC AND TRANSCENDENTAL EQUATIONS BY MICROSOFT EXCEL

27.41.15; 27.41.17; 27.41.41

NUMERICAL METHODS FOR SOLVING ALGEBRAIC AND TRANSCENDENTAL EQUATIONS BY MICROSOFT EXCEL

Автор: Кусов Анатолий Юрьевич, студент 3 курса ГБПОУ МО «Серпуховский колледж» г. Серпухов Московской области

Научный руководитель: Соколова Марина Александровна, преподаватель специальных дисциплин

Научный консультант: Щукина Ирина Евгеньевна, преподаватель иностранных языков

Аннотация.

Данная статья написана для студентов средних профессиональных учреждений, изучающих математические дисциплины в качестве профессиональных модулей. В ней описаны принципы решения алгебраических и трансцендентных уравнений, а также показан пример быстрого и удобного решения алгебраических и трансцендентных уравнений с помощью Microsoft Excel.

Annotation.

The article is written for secondary education students who study numerical analysis as a professional discipline. It describes the principles of solving algebraic and transcendental equations, and shows an example of a quick and easy solution of algebraic and transcendental equations using Microsoft Excel.

Ключевые слова: трансцендентные, алгебраические, уравнения, Эксель, численный анализ

Keywords: transcendental, algebraic, equation, Excel, numerical analysis

As a student who studies information technologies and mathematics I studied a lot of complex subjects and faced problems such as complex calculations which I had to do on my own as a task from a professor, so then I decided to find a way not to make calculation by myself. My professor of Numerical analysis allowed me to make an algorithm for an application software so that I shouldn’t have made these calculations myself.

Many current programs for solving types of problems are fee-paying i.e. Wolfram|Alpha, so I had to make it up by myself using affordable to me instruments.

The subject: Numerical analysis.

Study topic: Methods for solving algebraic and transcendental equations.

The purpose of the abstract: The purpose of the abstract is to study some methods of solving algebraic and transcendental equations by Microsoft Excel, to consider an example of practical solution of the equation by any of the methods.

The objectives of the abstract:

1. To analyse educational and popular science literature on this topic;
2. to research on some methods of solving algebraic and transcendental equations;
3. to implement some methods for solving algebraic and transcendental equations by Microsoft Excel.

A transcendental equation is an equation containing a transcendental function of the variable(s) being solved for.[6]

Microsoft Excel™ is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.[2]

In general, the process of solving the problem using a computer consists of the following steps:

1. Statement of the problem and constructing a mathematical model (modeling phase);
2. Choosing of a method and algorithm development (algorithms phase);
3. Writing the algorithm down in a computer-readable language (programming step);
4. Debugging and using of the computer program (implementation stage);
5. Analysis of the results (interpretation stage).

As an objective I have to solve a numerical problem: one needs to find out the approximate value of the equation given by the function y = f(x) with a specified accuracy and to separate the roots of the equation.

The composition of the task:

1. To read the theoretical part of the task;
2. to calculate selected data for an individual task in Microsoft Excel;
3. to make a presentation in Microsoft Power Point, including:

• a problem statement;
• a calculation algorithm;
• a table with calculation from Microsoft Office Excel, a graph of the original function;
• a result of the calculation and its analysis.

General statement of the problem

Find the real roots of the equation f(x) = 0, where f(x) – algebraic or transcendental function. Exact methods for solving equations are suitable only for a narrow class of equations.

The problem of numerical finding the roots of the equation consists of two stages:

1. Separation (localization) of the root;
2. Approximate evaluation of the root to a given precision (clarification of roots)

Root refinement[1]

If the sought root of the equation f(x) = 0 is separated, i.e. defined the segment [a, b], where exactly there is only one real root of the equation, then it is necessary to find the approximate value of the root with a given accuracy.

Root refinements can be made by various methods[2][4]:

1. Graphical method;
2. Bisection method;
3. Fixed-point iteration;
4. Secant method;
5. Newton's method;
6. Combined method.

Using tools of Microsoft Excel, we solved some numerical problems by the methods. Here’s the example of a solution of the above-stated methods and its schematic realisation (Figure 1.1) via Microsoft Excel.

Here’s the problem: one needs to find out the approximate value of the equation given by the function x^3 – 4x – 2 = 0 with an accuracy 𝜀 = 0.001 and to separate the roots of the equation. Solution: it is natural to use a graphical method. The graph of the function 𝑦 = 𝑓(𝑥) with the properties of the function gives a lot of information to determine the number of roots of the equation 𝑓(𝑥) = 0. The root of the equation 𝑥 = −0.539 on an interval а = −1, 𝑏 = −0.5.

Figure 1.1 – Microsoft Excel Snapshot

Results

The study has shown a way of solving algebraic and transcendental equation by graphical method via Microsoft Office Excel. The following methods of solving algebraic and transcendental equations were studied:

• Graphical method
• Bisection method;
• Fixed-point iteration;
• Secant method;
• Newton's method;

Conclusion

I have studied some methods of solving algebraic and transcendental equations via Microsoft Excel and considered an example of finding the root of the equation:

•        The simplest of the root refinement methods is the bisection method. Secant method uses proportional division of the interval. [3][4][5]

•        In Newton's method interval of the location of the root is not defined by the initial, and its initial value. [3][4][5]

•        Secant method and Newton's method have a common drawback: the accuracy of the value is checked at each step; [3][4][5]

•        I came to the conclusion that Microsoft Excel spreadsheet formulas are a very powerful computing tool that allows you to quickly and easily find the root of the equation than the manual calculation;

Список использованных источников

1. Lectures on Numerical Analysis /Dennis Deturck, Herbert S. Wilf. — 1-е издание. — Philadelphia: Department of Mathematics University of Pennsylvania, 2002. — 125с.
2. Microsoft Excel [Электронный ресурс] : Wikipedia, The Free Encyclopedia : Version ID: 920860350, retrieved: 16 October 2019 13:27 UTC / Wikipedia contributors // Wikipedia, The Free Encyclopedia. — Электрон. дан. — Сан-Франциско: Фонд Викимедиа, 2019. — Режим доступа: https://en.wikipedia.org/w/index.php?title=Microsoft_Excel&oldid=920860350;
3. Numerical analysis [Электронный ресурс] / Wikipedia contributors. — Электрон. текстовые дан. — San Francisco: Wikipedia, The Free Encyclopedia, 2019. — Режим доступа: https://en.wikipedia.org/w/index.php?title=Numerical_analysis&oldid=895278527, свободный. — Online encyclopedia (Дата обращения: 13.05.2019);
4. Numerical methods /John D. Fenton. — 1-е издание. — Vienna: Institute of Hydraulic Engineering and Water Resources Management. Vienna University of Technology, 2019. — 33с.;
5. Numerical Methods for Physicists [Электронный ресурс] / Anthony O’Hare. — Электрон. текстовые дан. — Belton: MMHB. Department of Computer Science and Engineering, 2005. — Режим доступа: http://mars.umhb.edu/~wgt/engr2311/NMfP.pdf, свободный (Дата обращения: 12.05.2019);
6. Transcendental equation [Электронный ресурс] / Wikipedia contributors. — Электрон. текстовые дан. — San Francisco: Wikipedia, The Free Encyclopedia, 2019. — Режим доступа: https://en.wikipedia.org/w/index.php?title=Transcendental_equation&oldid=902222843, свободный. — Online encyclopedia (Дата обращения: 5.10.2019).