Replication

Replication is a means of copying slots, usually number slots. It is particularly useful for copying formulae as it can update a formula for each slot into which it is copied.

In the following spreadsheet:

A B C D
1 Price ex VAT VAT Price inc VAT Total inc postage
2
3 £100.00 £17.50
4 £200.00
5 £300.00
6 £400.00
7 £500.00
8 £600.00
9
10 Postage charge £10.00

Slot B3 contains the formula A3*0.175. If you want to work out 17.5% of the remaining values in slots A4 to A8, you don't have to laboriously type formulae into slots B4 to B8. Instead, you can do the following:

1. Mark the block from B3 to B8.

2. Use the Replicate Down command:

Menu Command Ctrl-sequence Key
Blocks Replicate Down BRD

Your spreadsheet will now appear as follows:

A B C D
1 Price ex VAT VAT Price inc VAT Total inc postage
2
3 £100.00 £17.50
4 £200.00 £35.00
5 £300.00 £52.50
6 £400.00 £70.00
7 £500.00 £87.50
8 £600.00 £105.00
9
10 Postage charge £10.00

The formula in slot B3 has been copied into slots B4 to B8, but the slot reference in the formula has been updated for each of the slots in column B. Slot B4, for example, contains the formula A4*0.175. Slot B5 contains the formula A5*0.175.

To get the totals, all you have to do is the following:

1. Type the formula A3+B3 into slot C3. This gives the result 117.50

2. Mark the block from C3 to C8.

3. Give the Replicate Down command.

The spreadsheet will appear as follows:

A B C D
1 Price ex VAT VAT Price inc VAT Total inc postage
2
3 £100.00 £17.50 £117.50
4 £200.00 £35.00 £235.00
5 £300.00 £52.50 £352.50
6 £400.00 £70.00 £470.00
7 £500.00 £87.50 £587.50
8 £600.00 £105.00 £705.00
9
10 Postage charge £10.00

The formula in slot C3 has been copied into slots C4 to C8, but the slot reference in the formula has been updated for each of the slots in column C. Slot C4, for example, contains the formula A4+B4. Slot C5 contains the formula A5+B5.

There may be situations in which you don't want the slot reference(s) in the formula to be updated in the way described above. In the example above, you may want to add 10.00 to each total as the extra price for postage, for example. You would do this as follows:

1. In slot D3, type the formula C3+$B$10. This adds slot B10, ie 10.00 to slot C3 giving the result 127.50.

2. Mark the block from D3 to D8.

3. Give the Replicate Down command.

Your spreadsheet will look like this:

A B C D
1 Price ex VAT VAT Price inc VAT Total inc postage
2
3 £100.00 £17.50 £117.50 £127.50
4 £200.00 £35.00 £235.00 £245.00
5 £300.00 £52.50 £352.50 £362.50
6 £400.00 £70.00 £470.00 £480.00
7 £500.00 £87.50 £587.50 £597.50
8 £600.00 £105.00 £705.00 £715.00
9
10 Postage charge £10.00

This time, the formula in slot D3 has been copied into slots D4 to D8. However, the slot reference B10 has not been updated because it has been 'fixed' by the $ characters. The other slot reference in the formula in slot D3, C3, has been updated since it is not fixed by $ characters. Therefore, slot D4 contains the formula C4+$B$10, slot D5 contains the formula C5+$B$10, and so on.

Note that all references, including those fixed by $ characters, are updated correctly when you insert or delete rows or columns.

You need to put a $ character before each element of the slot reference you want to fix. Sometimes, you may only want to fix one element. For example, you may want the row references to be updated when copied but not the column references.

Consider the following document:

A B C D E
1 France Germany Spain England
2
3 Cars   - road sea road
4 TVs road air road road
5 Fridges sea road   - road
6 Post air air air air
7 Spares sea road sea road
8
9 Totals
10
11 air 1 2 1 1
12 sea 2 0 2 0
13 road 1 3 1 4

Slot B11 contains the formula

DCOUNTA(B$3B$7,B$3B$7=$A11)

DCOUNTA counts the number of slots in the range for which the condition is true. B$3B$7 is the range of slots which the DCOUNTA function is to act upon. B$3B$7=$A11 is the condition it has to satisfy. A11 contains the word 'air' so PipeDream counts all the slots in the range B3B7 which contain 'air'. This gives the required result 1.

If you now mark slots B11 to E11 and give the Replicate Right command

Menu Command Ctrl-sequence Key
Blocks Replicate Right BRR

PipeDream will count the occurrences of 'air' in the ranges C3C7, D3D7 and E3E7. When replicating, $ fixes any column or row it precedes. In this case, therefore, slot C11 contains the formula DCOUNTA(C$3C$7,C$3C$7=$A11), slot D11 contains the formula DCOUNTA (D$3D$7,D$3D$7=$A11), and so on. The references to column B have been updated, but the row references (3 and 7) have not been updated as they are fixed with $ characters.

Now mark slots B11 to E13 and give the Replicate Down command. Since there is no $ character, the row reference (11) is updated for each new row. Therefore, on row 12, the total occurrences of slots matching slot A12 (sea) are returned and on row 13, the total occurrences of slots matching slot A13 (road) are returned.

You can see that replication, together with the $ character, can dramatically increase the speed and accuracy with which you construct a spreadsheet.

In PipeDream, as in other spreadsheets, the term replication is generally used to refer to copying number slots. The differences between Copy Block and replication are as follows:

With the Replicate command, you can replicate a single slot to another position in the document. You can replicate a single slot to a range of slots. Or you can replicate a range of slots to another range of slots.

To Replicate a Single Slot

1. Give the Replicate command:

Menu Command Ctrl-sequence Key
Blocks Replicate BRE

2. When the dialogue box appears, on the Range to copy from line, type the slot reference of the single slot you wish to copy.

3. On the Range to copy to line, specify the reference of the slot you are copying to. The reference of the slot the caret is in will already appear.

4. Click the OK button or press Enter.

The contents of the first slot will appear replicated in the specified slot.

To Replicate a Range of Slots

1. Mark the range of slots.

2. Move the caret to where you want to copy the first slot in the range.

3. Give the Replicate command.

The first and last slots of the range you have marked will already appear on the Range to copy from line. If you had not marked the range, you could type in the required slot references at this stage. The slot references of the slot at which you want the copy of the range to start will appear in the Range to copy to line since the caret is in the slot, so all you need to do is to click the OK button or press Enter. If you had not positioned the caret in the right slot, you could type it in at this stage.

The range will be replicated at the specified position.

To Replicate a Range Into a Table

1. Mark the range of slots to be copied.

2. Give the Replicate command.

The source range will already be correct in the dialogue box since you have marked it.

3. Type the first and last slots of the target range on the Range to copy to line.

If the first range is a row, this must be a column, and vice versa.

4. Click the OK button or press Enter and the table will be created.

For information on See
Copying, moving or deleting blocks Block operations