NQ1, NQ2 - Spreadsheets and calculator iteration

NQ1, NQ2 - Spreadsheets and calculator iteration

Build and read a numerical calculation without losing its logic or its precision. You will copy spreadsheet formulae correctly, implement a supplied update and stopping test, generate iterates with a calculator's ANS facility, and distinguish stored values from displayed digits.

Copy a formula without moving the wrong cells

Three spreadsheet cells each display 0.330.33. Can their sum display 1.001.00?

It can. A display may hide digits still used in the calculation. We will return to that puzzle after making sure the formulae point to the right cells.

You already know

Function notation tells you what to substitute into an expression. A recurrence gives a sequence through a starting value and an update rule. Brackets determine which operations happen together.

A cell such as B4\text{B4} is in column B\text{B}, row 44. A formula begins with ==. In spreadsheet entries, ∗* means multiplication and // means division; use brackets for a numerator or denominator containing several terms.

Relative and absolute references

A relative reference, such as A2\text{A2}, moves with a copied formula. An absolute reference, such as \text{\A$2},keepsbothitscolumnandrowfixed.Adollarsignfixesthepartimmediatelyafterit:, keeps both its column and row fixed. A dollar sign fixes the part immediately after it: \text{$A2}fixesthecolumn,whilefixes the column, while\text{A$2}$ fixes the row.

For a formula moved one cell, the references change as follows.

Original referenceCopied one row downCopied one column right
A2\text{A2}A3\text{A3}B2\text{B2}
\text{\A$2}$\text{\A$2}$\text{\A$2}$
\text{\A2}$\text{\A3}$\text{\A2}$
\text{A\2}$\text{A\2}$\text{B\2}$

These spreadsheet conventions are not printed in the formula booklet. You need to recognise the references, not memorise any particular worksheet layout. Any familiar spreadsheet can be used during the course; calculator spreadsheet facilities are optional in the examination, rather than assumed.

[DIAGRAM: asset_name: A relative input and a fixed step; asset_slug: spreadsheet_references; caption: The previous value moves down a row while the fixed parameter stays in E2; description: Two panels compare formula A3=A2+EE2 before copying and A4=A3+EE2 after copying down. The relative input is blue, the fixed E2 parameter is sage and the destination is clay. Arrows show both input cells feeding the destination. E2 contains 0.25 in both panels.]
The previous value moves down a row while the fixed parameter stays in E2

Worked example 1: a fixed step size

Question. A worksheet has A2=1\text{A2}=1, E2=0.25\text{E2}=0.25, E3=0.50\text{E3}=0.50, and E4=0.75\text{E4}=0.75. Cell A3\text{A3} contains =\text{A2}+\text{\E$2}.Copyitdownto. Copy it down to \text{A5}.Statetheformulaeandvaluesin. State the formulae and values in \text{A3:A5}$.

Plan. Move the previous-value reference down each row, keeping the step-size cell fixed.

A3:=A2+$E$2⟶1.25first stepA4:=A3+$E$2⟶1.50copy down onceA5:=A4+$E$2⟶1.75same fixed increment\begin{aligned} \text{A3}:&\quad=\text{A2}+\text{\$E\$2}\quad\longrightarrow1.25 && \text{first step}\\ \text{A4}:&\quad=\text{A3}+\text{\$E\$2}\quad\longrightarrow1.50 && \text{copy down once}\\ \text{A5}:&\quad=\text{A4}+\text{\$E\$2}\quad\longrightarrow1.75 && \text{same fixed increment} \end{aligned}

Why this step? The calculation needs the previous row's value but the same parameter every time.

Answer. The three formulae above give 1.25,1.50,1.751.25,1.50,1.75.

Check. Every consecutive difference is 0.250.25; the entries in E3\text{E3} and E4\text{E4} are never used.

Change only the step-size reference from absolute to relative; leave every cell value unchanged.

Worked example 2: the parameter drifts

Question. Cell A3\text{A3} now contains =A2+E2=\text{A2}+\text{E2}, copied down to A5\text{A5}. Find the resulting formulae and values.

Plan. Move both relative references as the formula is copied.

A3:=A2+E2⟶1.25same first calculationA4:=A3+E3⟶1.75increment now 0.50A5:=A4+E4⟶2.50increment now 0.75\begin{aligned} \text{A3}:&\quad=\text{A2}+\text{E2}\quad\longrightarrow1.25 && \text{same first calculation}\\ \text{A4}:&\quad=\text{A3}+\text{E3}\quad\longrightarrow1.75 && \text{increment now }0.50\\ \text{A5}:&\quad=\text{A4}+\text{E4}\quad\longrightarrow2.50 && \text{increment now }0.75 \end{aligned}

Why this step? A formula can look correct in its first row but implement a different calculation after copying.

Answer. 1.25,1.75,2.501.25,1.75,2.50, with the cell formulae shown above.

Check. The first value agrees with the intended calculation, but the later differences reveal the moving parameter.

Absolute referencing is a choice about what must remain fixed, not a rule that every reference should have dollar signs.

Watch out

Wrong: anchor the previous value as well as the parameter, so every row recalculates from the original value. Right: keep the previous-value reference relative and anchor the fixed parameter.

Repair the formula in a new worksheet.

Translate an update and a stopping test into cells

A numerical procedure needs a starting value, an update, and instructions saying when to stop. A spreadsheet makes each step visible in a separate row.

In a new worksheet, store a parameter cc in F2\text{F2} and a tolerance τ>0\tau>0 in F3\text{F3}. Consider the supplied update

xn+1=(xn+c)/2x_{n+1}=(x_n+c)/2

We are implementing this rule; the derivation and justification of particular equation-solving methods come later.

Successive-change test

For n≥1n\ge1, define

dn=∣xn−xn−1∣d_n=|x_n-x_{n-1}|

The instruction “stop at the first dn<τd_n<\tau” can be implemented by a flag which is 11 when the condition is true and 00 otherwise.

This tests the change between iterates. It does not, by itself, bound the distance to an unknown answer.

Not in the formula booklet: learn it.

The spreadsheet function ABS⁡\operatorname{ABS} returns a non-negative magnitude. The function IF⁡(condition,value if true,value if false)\operatorname{IF}(\text{condition},\text{value if true},\text{value if false}) selects one of two outputs. These function names and the reference procedure are not supplied in the booklet.

CellEntryMeaning
A2\text{A2}00Starting index
B2\text{B2}x0x_0Starting value
A3\text{A3}=A2+1=\text{A2}+1Increase the index
B3\text{B3}=(\text{B2}+\text{\F$2})/2$Apply the supplied update
C3\text{C3}=ABS⁡(B3−B2)=\operatorname{ABS}(\text{B3}-\text{B2})Magnitude of the latest change
D3\text{D3}=\operatorname{IF}(\text{C3}<\text{\F$3},1,0)$Stop flag

Copy row 33 down. No change is defined in the starting row, because it has no previous iterate. The first flag of 11 identifies the first row satisfying the supplied rule.

Worked example 3: the first stop flag

Question. Use x0=1x_0=1, c=3c=3, and τ=0.3\tau=0.3. Find the first iterate meeting dn<τd_n<\tau.

Plan. Apply the update, calculate the magnitude of each change, and stop at the first true condition.

x1=(1+3)/2=2,d1=1flag 0x2=(2+3)/2=2.5,d2=0.5flag 0x3=(2.5+3)/2=2.75,d3=0.25flag 1\begin{aligned} x_1&=(1+3)/2=2,\quad d_1=1 && \text{flag }0\\ x_2&=(2+3)/2=2.5,\quad d_2=0.5 && \text{flag }0\\ x_3&=(2.5+3)/2=2.75,\quad d_3=0.25 && \text{flag }1 \end{aligned}

Why this step? The test uses the change just calculated, not the value of xnx_n itself.

Answer. First stop at n=3n=3, with x3=2.75x_3=2.75; this is spreadsheet row 55.

Check. 0.25<0.30.25<0.3, whereas the preceding change 0.50.5 does not pass.

Change only the tolerance from 0.30.3 to 0.10.1. The iterates stay the same, but the stop flag changes.

Worked example 4: a stricter tolerance

Question. With x0=1x_0=1 and c=3c=3, find the first iterate meeting dn<0.1d_n<0.1.

Plan. Retain the update values and continue until the smaller threshold is crossed.

d1=1,d2=0.5,d3=0.25all failx4=(2.75+3)/2=2.875,d4=0.125still failsx5=(2.875+3)/2=2.9375,d5=0.0625first success\begin{aligned} d_1&=1,\quad d_2=0.5,\quad d_3=0.25 && \text{all fail}\\ x_4&=(2.75+3)/2=2.875,\quad d_4=0.125 && \text{still fails}\\ x_5&=(2.875+3)/2=2.9375,\quad d_5=0.0625 && \text{first success} \end{aligned}

Why this step? A smaller tolerance changes the stopping decision, not the recurrence or the values already calculated.

Answer. n=5n=5, x5=2.9375x_5=2.9375, in spreadsheet row 77.

Check. 0.0625<0.1<0.1250.0625<0.1<0.125, so this is the first passing row.

An IF⁡\operatorname{IF} formula must follow the given boundary convention. Replacing << by ≤\le changes the decision when a change equals the tolerance.

Read derived output columns through their formulae too. Here =C4/C3=\text{C4}/\text{C3} would give 0.5/1=0.50.5/1=0.5, the ratio of two successive change magnitudes. Later lessons use such output to discuss convergence and order, with the justification appropriate to the method.

Continue this lesson.

You’re previewing NovaLearn without an account. Create a free account to read the rest.

Create free account