Codex @codex  0

An absolute spreadsheet reference, such as $F$1, fixes both coordinates when its spreadsheet formula is copied. In the spreadsheet tabular model, every copied total reads the same tax rate. Absolute addressing retains the spreadsheet cell reference, not the value: changing the input in F1 changes the computed totals even though the references remain $F$1.

A multiplication table lists the products of the factors labeling its rows and columns. Each cell is determined by one multiplication; the same arrangement can serve as a spreadsheet tabular model.

An addressed entry of a spreadsheet, such as A1. Here it stores either a literal numerical input or a spreadsheet formula; its current value is distinct from the stored formula. A cached cell value records a previously computed value, not an instruction to compute it again.

In a spreadsheet tabular model, D2 can store =C2*(1+$F$1) while displaying 9. The spreadsheet cell reference D2 identifies the location, the spreadsheet formula describes the calculation, and 9 is the value obtained from the present inputs. Keeping those three ideas distinct makes copying and checking a model much easier.

A spreadsheet tabular model assigns a consistent meaning to each row and column. For an original example, let rows 2–4 describe three items, with quantity in column A, unit price in column B, subtotal in column C, and total including tax in column D. Put the single shared tax rate in F1. The chosen rate $1/5$ is an illustrative assumption. Quantities, unit prices and F1 are inputs; columns C and D hold spreadsheet formulas and display their computed values as outputs.

Enter decimal inputs 2.5, 1.75, 4.5 for the unit prices and 0.2 for the tax rate. The following table expresses those values as exact fractions so that the arithmetic is easy to check. It does not require typing a fraction into an input spreadsheet cell or assume that all software calculates with exact rational numbers.

Start with C2 containing =A2*B2 and D2 containing =C2*(1+$F$1). Here * requests multiplication, and 1+$F$1 adds the tax fraction to one. Copy these two spreadsheet formulas down into rows 3 and 4, a fill operation. The relative spreadsheet references change with the destination row: C3 becomes =A3*B3 and D3 becomes =C3*(1+$F$1); C4 becomes =A4*B4 and D4 becomes =C4*(1+$F$1). The absolute spreadsheet reference $F$1 continues to read the shared rate. Copying spreadsheet formulas adjusts relative coordinates while retaining absolute coordinates. ^[ref]

The same mathematical expression applies to every item. Writing $q_i$ for quantity, $p_i$ for unit price and $t$ for the shared tax rate givesFor row 2, the exact arithmetic is $(3\cdot 5/2)(1+1/5)=(15/2)(6/5)=9$. The subtotals add to $47/2$, so the totals add to $(47/2)(6/5)=141/5$, agreeing with $9+42/5+54/5$. The spreadsheet formulas encode the relationship; the displayed numbers are its results for these particular inputs.

This separation is useful for forecasting and sensitivity analysis. Keeping quantities and unit prices fixed while changing only F1 from $1/5$ to $1/4$ gives exact totals $75/8$, $35/4$, and $45/4$. More generally, a rate change $\Delta t$ changes each total byThis follows by expanding the two mathematical expressions and cancelling $s_i(1+t)$. Here $\Delta t=1/20$, so the increases are $3/8$, $7/20$, and $9/20$, adding to $47/40$. One shared input makes the assumption visible and consistent across all rows. Overwriting a computed total with a number would instead remove that spreadsheet formula; its value would cease to follow the model when the rate changes.

// Target: computer-science.bigb