As you have seen, a PipeDream window is divided into slots formed where columns and rows meet.
Much of the power of PipeDream lies in the fact that you can create different types of slot:
Number slots can contain virtually any mathematical formula and can include references to other slots.
The best way to learn about number slots and PipeDream's spreadsheet facilities is to put them into practice by building a spreadsheet yourself. The spreadsheet you are going to build is shown here:
A | B | C | D | |
---|---|---|---|---|
1 | Item | Value | VAT | Total |
2 | ||||
3 | Computer | £500.00 | £87.50 | £587.50 |
4 | Monitor | £200.00 | £35.00 | £235.00 |
5 | Disc drive | £250.00 | £43.75 | £293.75 |
6 | Printer | £350.00 | £61.25 | £411.25 |
7 | Draw Program | £120.00 | £21.00 | £141.00 |
8 | ||||
9 | Total | £1420.00 | £248.50 | £1668.50 |
10 | ||||
11 | Offer price | £1200.00 | £210.00 | £1410.00 |
12 | Saving made | £220.00 | £38.50 | £258.50 |
PipeDream has a template file which contains the settings best suited to spreadsheet work. It tells PipeDream that all new slots are number slots, unless they contain only text, and ensures that each column is formatted separately. It also turns on the grid to make your spreadsheet easier to read.
A new PipeDream document window appears on the screen ready for you to enter your spreadsheet.
Click Select on the toggle size icon so that the PipeDream document window fills the screen.
As usual when you start a new PipeDream document window, the caret is in slot A1.
First of all, you need to type in the names of the items whose prices the spreadsheet will manipulate. Type the first name as follows:
Item
You will see the word you have typed appear in the formula line at the top of the window. This is where PipeDream displays the contents of number slots. The Numbers template file has told PipeDream to expect numbers.
If you make any typing mistakes, you can carry out corrections on the formula line. Position the caret in the formula line by moving the pointer to it and clicking Select. You can use the following keys in the formula line:
← | to move the caret one character to the left |
→ | to move the caret one character to the right |
Shift-← | to move the caret left one word |
Shift-→ | to move the caret right one word |
Ctrl-← | to move the caret to the beginning of the line |
Ctrl-→ | to move the caret to the end of the line |
Delete Backspace |
to delete the character to the left of the caret |
Copy | to delete the character to the right of the caret |
Ctrl-Copy | to delete forward to the end of the line |
Ctrl-U | to delete everything on the line |
When you are happy with your entry in the formula line and you are ready to enter it in the slot, press Enter.
The word that you typed appears in your document in a text slot. PipeDream wasn't able to make sense of it as a formula so it put the word into a text slot. Type in the names of the remaining items in column A, remembering to move on to the next line after each entry.
Position the caret in slot A1 by moving the pointer to it and pressing Select.
Press Tab to move the caret into slot B1. You will see that column B's border is now displayed in a different colour.
Type the word Value and press Tab. The caret is now in slot C1. Column C's border is displayed in a different colour.
Type VAT and press Tab to position the caret in slot D1.
Type the word Total.
Position the caret in slot B3. You can do this either by moving the pointer to it and clicking Ctrl-Select or by clicking Select and then pressing Tab.
Type 500.
The contents of slot B3 are displayed in the formula line.
Press Enter. Slot B3 is a number slot containing the number 500.00. Numbers in PipeDream are automatically displayed with two decimal places although you can change this if you want to.
Press Tab to position the caret in slot C3.
Type B3 * 0.175. This formula multiplies the contents of slot B3 by 0.175 to work out the VAT.
Click Select on the tick box to the left of the formula line. This has the same effect as pressing Enter. Slot C3 now displays the result of the formula, ie 87.50. The formula line displays the formula itself so that you can edit it if necessary.
Press Tab to position the caret in slot D3.
Move the pointer to the formula line and click Select to position the caret there. Now move the pointer to slot B3 and click Select. B3 appears in the formula line. Type +. Now move the pointer to slot C3 and click Select. C3 appears in the formula line, so that the formula line now reads B3+C3. Click on the tick box or press Enter. The total price appears in slot D3, 587.50.
You may have noticed whilst typing the above that the words Value, VAT and Total are not aligned with the numbers beneath them. This is because text slots are automatically left-aligned whilst number slots are automatically right-aligned. However, it will look better if the headings are right-aligned so that they line up with the numbers. The simplest way to right align slots B1, C1 and D1 is:
Mark the slots by dragging Select across them.
Give the Right Align command:
Menu | Command | Ctrl-sequence | Key |
---|---|---|---|
Layout | Right align | LAR |
The column headings will now line up with the numbers beneath them.
If you make a mistake at any point and end up with something you do not want in the formula line, press Esc or click Ctrl-Select on the cross icon to the left of the formula line.
Position the caret in slot B4 by moving the pointer to it and clicking Ctrl-Select, then type 200.
Press Enter twice and type 250. Continue to type the rest of the values down to slot B7.
The next thing to do is to work out the VAT of the other prices in addition to that of the computer.
Position the caret in slot C3 by moving the pointer to it and clicking Select.
Now drag Select to slot C7 so that slots C3 to C7 inclusive are marked as a block and displayed on a black background.
Give the Replicate Down command:
Menu | Command | Ctrl-sequence | Key |
---|---|---|---|
Blocks | Replicate down | BRD |
PipeDream replicates the formula you typed into slot C3 (B3 * 0.175) to all the slots in the block, ie PipeDream copies the formula, updating the slot reference in the formula as required. Slot C4, for example, contains the formula B4 * 0.175.
Clear the marked block by clicking Select in the mark block box.
In the same way, the formula in slot D3 can be replicated into slots D4 to D7.
Mark the block from D3 to D7 inclusive.
Give the Replicate Down command.
Clear the marked block by clicking Select in the mark block box.
Position the caret in slot B9 by moving the caret to it and clicking Ctrl-Select.
Type SUM(B3B7) and either click Select on the tick box or press Enter. The total value of all the items appears in slot B9.
SUM is a function which adds together the values of all slots in the range you type in. It is the equivalent of typing B3+B4+B5+B6+B7 - and much quicker!
Drag Select from slot B9 to slot D9 to mark slots B9, C9 and D9 as a block.
Give the Replicate Right command:
Menu | Command | Ctrl-sequence | Key |
---|---|---|---|
Blocks | Replicate right | BRR |
The formula in slot B9, SUM(B3B7), is replicated into slots C9 and D9 and updated accordingly. Slot C9 contains SUM(C3C7) and slot D9 contains SUM(D3D7).
Click Select in the mark block box to clear the marked block.
Position the caret in slot B11 by positioning the caret in it and clicking Ctrl-Select.
Type 1200 and either click Select on the tick box or press Enter.
Position the caret in slot B12 and type D9-B11.
When you click Select on the tick box or press Enter, PipeDream displays the result of the formula which tells you the difference between the total price for all the items and the sale price and so lets you know how much money you are going to save.
One reason why spreadsheets are so useful is that they are dynamic, ie changing one entry changes all associated entries automatically.
Position the caret in slot B3, type 600 and press Enter.
Notice that all slots which refer to slot B3 change to suit the new contents of slot B3.
Type 500 and press Enter and the values in slots C3, D3, B9, C9, D9 and B12 will all change again.
Try changing some values and see how your changes affect the spreadsheet.
As the figures in this spreadsheet are all prices, it would be appropriate for them all to have a £ sign in front of them. Mark the document by clicking Select in the mark block box.
Give the Leading Characters command:
Menu | Command | Ctrl-sequence | Key |
---|---|---|---|
Layout | Leading characters | LCL |
All the number slots in the document now have a £ sign as their first, ie leading character.
Clear the marked block by clicking Select in the mark block box.
If you wish, you can specify which characters should be used as the leading characters:
Give the Options command.
On the Leading characters line, you will see a box containing the £ sign. This is the leading character that is set when you start PipeDream.
Click Select in the Leading characters box just after the £ sign.
Delete the £ sign using the Backspace key and type $.
Click the Enter. Now all the number slots have $ as their leading character.
button or pressYou may want to give the Options command again and change the leading character back to £.
When working with spreadsheets, you will often want to adjust the widths of columns to match the numbers they contain. The quickest and easiest way to do this with PipeDream is with Auto Width:
First, mark the whole document by clicking Select in the mark block box.
Give the Auto Width command:
Menu | Command | Ctrl-sequence | Key |
---|---|---|---|
Layout | Auto width | LAW |
Each column is now the width necessary for the widest entry in that column.
Clear the marked block by clicking Select in the mark block box.
This facility can be particularly useful when creating very big spreadsheets with many columns.
Now that your spreadsheet is complete, you can save it if you want to do so.