A simple model in a spreadsheet might be to compute multiplication tables. We reserve a cell for the unit of the table and construct outputs for the products. The solution introduces some important features of spreadsheets.
A simple approach would be to enter a number and add the number to itself in the next cell. For example, enter 5 and then, in the next cell, enter =cell + 5. Drag this formula into the next cells to produce a sequence such as 5, 10, 15...
To use multiplication in a formula you will need a sequence 1..12 or whatever in the first column. If these are in column A then column B would contain the formula =A1*5 (or 6, 7, etc).
To produce a complete table requires a bit more work and a new technique, the fixed or absolute reference. When a formula is drag-copied its column or row letter or number changes. There are times, however, when a cell reference needs to stay the same, for example where a single percentage value is to be applied to a number of data rows. For a multiplication table the static value is the multiplicand, the value being multiplied.
A single number is entered into cell B2, the processing and output are done in column E. To obtain any multiplication table the user simply enters the value required in cell B2.
You might think that you could proceed by entering a reference to cell B2 in C3 and then copying it into C4, C5, etc. This, however, will not work because the contents of C4 will be B3, the contents of C5 will be B4, and so on.
To get the contents of B2 into C3-C8 we set C3 to =$B$2 and then drag-copy it into C4, C5, etc. The '$' symbols fixes the reference to the cell B2 so neither the column reference (B) nor the row reference (2) change during the drag-copy process.
Now you can enter =A3*C3 into E3 and copy it into the cells underneath and get the right result.
A reference like 'A3' is called a relative reference; a reference like '$B$2' is called an absolute reference.
To produce a full multiplication table the formula needs to include both fixed (absolute) and variable (relative) references.
The formula that is required in B2 is one that will, when dragged down, keep the cell B1 fixed to produce 1x1, 2x1, 3x1, etc. and, when dragged across, will keep A2 fixed to produce 1x1, 1x2, 1x3, etc. The formula required is =$A2*B$1. The left part of the expression keeps A2 fixed for the drag-copy across row 2. The right part of the expression keeps B1 fixed for the downward drag-copy.
The table can be completed with just two drag-copy actions, one through row 2 (or column B) and the other, with the cells still selected, through the remaining cells. Examine the formulas in the cells to confirm how they work.
You have now identified four forms of reference in formulas, relative, absolute, column-fixed and row-fixed. When entering a formula use the F4 key to cycle between these four forms and insert the dollar symbols in the required place.
Use the Back Button to Return to the previous page