Bisection Method Calculator in Excel

The setup of the bisection method is about doing a specific task in Excel. With the bisection method, we are going to find the root of a given function.

The bisection method allows you to find the root of any function in a given search interval. In this Excel tutorial, you will calculate guesses, middle point and error.

The method works by dividing the interval that contains the root into two equal parts and then determining which part of the interval contains the root. This process is repeated until the root is found with a desired degree of accuracy.

Finding roots

The first step in creating a Bisection Method calculator in Excel is to define the function for which you want to find the root. This function should be entered into a cell in the Excel worksheet.

For example, suppose you want to find the root of the following function:

f(x) = 2e^x-4x+2

Present the function and two possible roots.

two possible roots

Make some assumptions.

assumptions

Place three different roots beside the guesses.

three roots

In addition to F(c), enter the tolerance, which is the maximum acceptable error in the estimate of the root. This tolerance should be entered into a cell in the Excel worksheet.

error

Decide the value that should be accurate besides the error.

For example, suppose you want to find the root with a tolerance of 3. To define this tolerance in Excel, you can enter the value 3 into a cell, such as cell H5.

value

Calculating bisection method

The next step is to create the Bisection Method formula in Excel. This formula will be used to calculate the midpoint of the interval and to determine which part of the interval contains the root.

See also  Square Root in Excel

In the cell under f(a), type in =2*exp(a6)-5*a6+2.

formula

Note: The 2 in front of the formula in this step is the one we placed at the beginning.

Drag the small square from f(a) to f(c).

drag square

Click on the cell below the error, type =ABS(B6), and then hit enter.

abs function

Click under the cell with 3 in it (1), and type in =IF (G6=3;1(true);0(false)), and then press enter.

if function

Mark the row with values in it.

mark row

Click on the small square showing on the right lower corner, and keep dragging it down until the value under 3 stably shows 1.

drag down

The final step is to test the Bisection Method calculator in Excel. To test the calculator, simply enter the values for the function, interval, and tolerance into the appropriate cells.

The Bisection Method calculator in Excel will continue to repeat the Bisection Method formula until the root is found or until the tolerance is reached. The root of the function will be displayed in the “E” column.