Spreadsheets

As you have seen, a PipeDream document window is divided into cells formed where columns and rows meet.

Much of the power of PipeDream lies in the fact that you can create different types of cell:

Number cells can contain virtually any mathematical formula and can include references to other cells.

The best way to learn about number cells 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 cells are number cells, 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.

  1. Click Menu on the PipeDream icon on the icon bar.
  2. Click the New document option.
  3. Click the Numbers option.

A new PipeDream document window appears on the screen ready for you to enter your spreadsheet.

Click 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 cell A1.

Building the Spreadsheet

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 cells. 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. 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
Cmd-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 cell, click the Tick toolbar button (to the left of the formula line) or press Return↵.

The word that you typed appears in your document in a text cell. PipeDream wasn’t able to make sense of it as a formula so it put the word into a text cell. 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 cell A1 by moving the pointer to it and clicking.

Press Tab to move the caret into cell 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 cell C1. Column C’s border is displayed in a different colour.

Type VAT and press Tab to position the caret in cell D1.

Type the word Total.

Position the caret in cell 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 cell B3 are displayed in the formula line.

Click the Tick toolbar button or press Return↵. Cell B3 is a number cell 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 cell C3.

Type B3 * 0.175. This formula multiplies the contents of cell B3 by 0.175 to work out the VAT. (Note: this example was written in 1991 when the VAT rate was 17.5%)

Click the Tick toolbar button or press Return↵. Cell C3 now displays the result of the formula, i.e. 87.50. The formula line displays the formula itself so that you can edit it if necessary.

Press Tab to position the caret in cell D3.

Move the pointer to the formula line and click to position the caret there. Now move the pointer to cell B3 and click. B3 appears in the formula line. Type +. Now move the pointer to cell C3 and click. C3 appears in the formula line, so that the formula line now reads B3+C3. Click the Tick toolbar button or press Return↵. The total price appears in cell D3, 587.50.

Alignment

You may have noticed whilst typing the above that the heading words Value, VAT and Total are not aligned with the numbers beneath them. This is because text cells are automatically left-aligned whilst number cells 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 cells B1, C1 and D1 is:

  1. Mark the cells by dragging across them.
  2. Give the Right Align command:
Menu Command Tool Key Cmd-sequence
Layout Right align Right align icon 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, click the Cross toolbar button (to the left of the formula line) or press Escape.

Position the caret in cell B4 by moving the pointer to it and clicking Ctrl-Select, then type 200.

Press Return↵ twice and type 250. Continue to type the rest of the values down to cell 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 cell C3 by moving the pointer to it and clicking

Now drag to cell C7 so that cells C3 to C7 inclusive are marked as a block and displayed with a black background.

Give the Replicate Down command:

Menu Command Tool Key Cmd-sequence
Blocks Replicate down Fill down icon BRD

PipeDream replicates the formula you typed into cell C3 (B3 * 0.175) to all the cells in the block, i.e. PipeDream copies the formula, updating the cell reference in the formula as required. Cell C4, for example, now contains the formula B4 * 0.175.

Clear the marked block by clicking the mark block box.

In the same way, the formula in cell D3 can be replicated into cells D4 to D7.

Mark the block from D3 to D7 inclusive.

Give the Replicate Down command.

Clear the marked block by clicking the mark block box.

Totals

Position the caret in cell B9 by moving the pointer to it and clicking Ctrl-Select.

Type SUM(B3B7) and either click the Tick toolbar button or press Return↵. The total value of all the items appears in cell B9.

SUM is a function which adds together the values of all cells in the range you type in. Here it is the equivalent of typing B3+B4+B5+B6+B7 - and so much quicker and more accurate for larger ranges!

Drag from cell B9 to cell D9 to mark cells B9, C9 and D9 as a block.

Give the Replicate Right command:

Menu Command Tool Key Cmd-sequence
Blocks Replicate right Fill right icon BRR

The formula in cell B9, SUM(B3B7), is replicated into cells C9 and D9 and updated accordingly. Cell C9 contains SUM(C3C7) and cell D9 contains SUM(D3D7).

Clear the marked block by clicking the mark block box.

Position the caret in cell B11 by moving the pointer to it and clicking Ctrl-Select.

Type 1200 and either click the Tick toolbar button or press Return↵.

Position the caret in cell B12 and type D9-B11.

When you click the Tick button or press Return↵, 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.

Recalculating Your Spreadsheet

One reason why spreadsheets are so useful is that they are dynamic, i.e. changing one entry changes all associated entries automatically.

Position the caret in cell B3, type 600 and press Return↵.

Notice that all cells which refer to cell B3 change to suit the new contents of cell B3.

Type 500 and press Return↵ and the values in cells C3, D3, B9, C9, D9 and B12 will all change again.

Try changing some values and see how your changes affect the spreadsheet.

Leading Characters

As the figures in this spreadsheet are all prices, it would be appropriate for them all to have a currency sign in front of them. Mark the whole document by clicking the mark block box.

Give the Leading Characters command:

Menu Command Tool Key Cmd-sequence
Layout Leading characters Leading characters icon LCL

All the number cells in the document now have a £ sign as their first, i.e. leading character.

Clear the marked block by clicking 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 an input field containing the £ sign. This is the leading character that is set when you start PipeDream.

Click in the Leading characters input field just after the £ sign.

Delete the £ sign using the Backspace key and type $.

Click the OK button or press Return↵. Now all the number cells have $ as their leading character.

You may want to give the Options command again and change the leading character back to £.

Column Widths

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 the mark block box.

Give the Auto Width command:

Menu Command Tool Key Cmd-sequence
Layout Auto width LAW

Each column is now the width necessary for the widest entry in that column.

Clear the marked block by clicking 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.