Spreadsheets

As you have seen, a PipeDream 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 Select on the New document option.
  3. Click Select on the Numbers option.

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 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 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 cell, press Enter.

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 pressing Select.

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.

Press Enter. 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.

Click Select on the tick box to the left of the formula line. This has the same effect as pressing Enter. 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 Select to position the caret there. Now move the pointer to cell B3 and click Select. B3 appears in the formula line. Type +. Now move the pointer to cell 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 cell D3, 587.50.

Alignment

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 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:

Mark the cells 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 cell 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 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 Select.

Now drag Select to cell C7 so that cells 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 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, 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 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 Select in the mark block box.

Totals

Position the caret in cell 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 cell B9.

SUM is a function which adds together the values of all cells in the range you type in. It is the equivalent of typing B3+B4+B5+B6+B7 - and much quicker!

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

Give the Replicate Right command:

Menu Command Ctrl-sequence Key
Blocks Replicate right 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).

Click Select in the mark block box to clear the marked block.

Position the caret in cell 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 cell 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.

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 Enter.

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

Type 500 and press Enter 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 £ 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 cells in the document now have a £ sign as their first, i.e. 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 OK button or press Enter. 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 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.