Interval Bisection from Spreadsheet

Questions that present interval bisection data in spreadsheet format and require interpretation or continuation of the bisection process from given values.

3 questions

OCR MEI Further Numerical Methods 2024 June Q5
5 The root of the equation \(\mathrm { f } ( x ) = 0\) is being found using the method of interval bisection. Some of the associated spreadsheet output is shown in the table below.
1ABCDEF
1af(a)\(b\)f(b)c\(\mathrm { f } ( c )\)
22-0.610936.085542.51.43249
32-0.61092.51.432492.250.17524
42-0.61092.250.175242.125-0.2677
52.125-0.26772.250.175242.1875-0.0598
6
The formula in cell B2 is \(\quad = \mathrm { EXP } ( \mathrm { A } 2 ) - \mathrm { A } 2 ^ { \wedge } 2 - \mathrm { A } 2 - 2\).
  1. Write down the equation whose root is being found.
  2. Write down a suitable formula for cell E2. The formula in cell A3 is $$= \mathrm { IF } ( \mathrm {~F} 2 < 0 , \mathrm { E } 2 , \mathrm {~A} 2 )$$ .
  3. Write down a similar formula for cell C3.
  4. Complete row 6 of the table on the copy in the Printed Answer Booklet.
  5. Without doing any calculations, write down the value of the root correct to the number of decimal places which seems justified. You must explain the precision quoted.
  6. Determine how many more applications of the bisection method are needed such that the interval which contains the root is less than 0.0005 .
OCR MEI Further Numerical Methods 2020 November Q6
6 Fig. 6.1 shows the graph of \(y = \mathrm { e } ^ { 3 x } - 11 x - 0.5\) for \(- 0.5 \leqslant x \leqslant 1\). \begin{figure}[h]
\includegraphics[alt={},max width=\textwidth]{87bb8eb7-b725-48b0-b32b-0bfce624cd91-08_576_881_315_333} \captionsetup{labelformat=empty} \caption{Fig. 6.1}
\end{figure} The equation \(\mathrm { e } ^ { 3 x } - 11 x - 0.5 = 0\) has two roots, \(\alpha\) and \(\beta\), such that \(\alpha < \beta\). Dennis is going to use the method of interval bisection with starting values denoted by \(a\) and \(b\).
  1. Explain why the method of interval bisection starting with \(a = 0\) and \(b = 1\) may not be used to find either \(\alpha\) or \(\beta\). Dennis uses the method of interval bisection starting with \(a = 0\) and \(b = 0.5\) to find \(\alpha\). Some spreadsheet output is shown in Fig. 6.2. \begin{table}[h]
    ABCDEF
    1af(a)bf(b)\(x _ { \text {new } }\)\(\mathrm { f } \left( x _ { \text {new } } \right)\)
    200.50.5-1.518310.25-1.133
    300.50.25-1.1330.125-0.42
    400.50.125-0.420010.06250.01873
    50.06250.018730.125-0.420010.09375-0.2065
    \captionsetup{labelformat=empty} \caption{Fig. 6.2}
    \end{table} Dennis states that the formula in cell B2 is $$= \operatorname { EXP } \left( 3 ^ { * } \mathrm {~A} 1 \right) - 11 \mathrm {~A} 2 - 0.5$$ Dennis has made two errors.
  2. Write a correct version of Dennis's formula for cell B2. The formula in cell A3, which is correct, is
    = IF(F2 > 0, E2, A2)
  3. Write a suitable formula for cell C3.
  4. Use the information in Fig. 6.2 to
    • find the value of \(\alpha\) as accurately as possible,
    • state the maximum possible error in this estimate.
    Liren uses a different method to find a sequence of estimates of the value of \(\beta\) using a spreadsheet. The output, together with some further analysis, is shown in Fig. 6.3. \begin{table}[h]
    ABCD
    1\(x\)f(x)differenceratio
    20.4-1.5799
    30.6-1.0504
    40.996718.4245
    50.64398-0.6809-0.35273
    60.67036-0.40260.026378-0.0748
    70.708520.083860.0381641.44682
    80.70194-0.0075-0.00658-0.1724
    90.70248-0.00010.00054-0.082
    100.70249\(1.8 \mathrm { E } - 07\)\(8.88 \mathrm { E } - 06\)0.01646
    \captionsetup{labelformat=empty} \caption{Fig. 6.3}
    \end{table} The formula in cell A4 is $$= ( \mathrm { A } 2 * \mathrm {~B} 3 - \mathrm { A } 3 * \mathrm {~B} 2 ) / ( \mathrm { B } 3 - \mathrm { B } 2 )$$
  5. State the method being used.
  6. Explain what the values in column D tell you about the order of convergence of this sequence of estimates. Liren states that \(\beta = 0.70249\) correct to 5 decimal places.
  7. Determine whether Liren is correct.
OCR MEI Further Numerical Methods Specimen Q2
2 The following spreadsheet printout shows the bisection method being applied to the equation \(\mathrm { f } ( x ) = 0\), where \(\mathrm { f } ( x ) = \mathrm { e } ^ { x } - x ^ { 2 } - 2\). Some values of \(\mathrm { f } ( x )\) are shown in columns B and D.
ABCDEFG
1a\(\mathrm { f } ( a )\)bf(b)\(( a + b ) / 2\)\(\mathrm { f } ( ( a + b ) / 2 )\)mpe
21-0.2817221.3890561.50.2316890.5
31-0.281721.50.2316891.25-0.0721570.25
41.25-0.072161.50.2316891.3750.0644520.125
51.25-0.072161.3750.0644521.3125-0.0072060.0625
61.3125-0.007211.3750.0644521.343750.0277280.03125
  1. The formula in cell A 3 is \(= \mathrm { IF } ( \mathrm { F } 2 > 0\), A2, E2). State the purpose of this formula.
  2. The formula in cell C 3 is \(= \mathrm { IF } ( \mathrm { F } 2 > 0 , \ldots , \ldots )\). What are the missing cell references?
  3. In which row is the magnitude of the maximum possible error (mpe) less than \(5 \times 10 ^ { - 7 }\) for the first time?