Spreadsheets are used for organising numerical data and are ideally suited to making multiple calculations from a few inputs. The spreadsheet I’m using for this tutorial is provided by Open Office, and can be downloaded for free from www.openoffice.org

Other spreadsheets are freely available. The techniques shown in this tutorial will almost certainly be transferable to any others you might come across.


A Simple Scale Calculator
Any of the cells can contain various numbers, text, images etc. and formulae. Lets start off by making a simple spreadsheet to calculate various scales.

To start, we need to input two numbers: the measurement and the scale we want. The cells we are going to input the data into can be labelled with text. We also want a cell in which the output will appear.

Fig.1

In fig.1, text has been entered into cells B3, B6 and B9. It doesn’t matter which cells you use. Organise the spreadsheet to suit you. Column B has been made wider to accept a line of text that exceeds its width. Make columns wider by clicking on the dividing line between columns and dragging to the required width.

The cells that will contain the inputs and output have been given a background colour (fig.2).

1. Highlight the cell or cells you wish to change. To select multiple cells, hold down the control key while you click.
2. Right click inside one of them. Select ‘Format Cells’ from the context menu that appears.
3. Select the ‘Background’ tab.
4. Choose the desired background colour.

Fig.2

Now we’re going to enter a formula into cell C9.

We want to take a measurement and divided it by the scale to get the required scaled measurement. So, the figure entered into cell C3 needs to be divided by the scale entered into C6. The result needs to appear in cell C9.

1. Select C9
2. Enter the formula: =C3/C6

The ‘=’ means a formula will be entered in this cell. The ‘/’ means divide. Don’t worry about the #VALUE! message that might appear in the cell. It just means a formula is waiting for data.

Let’s test the formula. Enter ‘128’ into cell C3 and ‘64’ into cell C6. Using simple figures let’s us easily test the formula to make sure it’s working as we intended. The output of ‘2’ shows us that it is (Fig. 3).

Fig.3

 

A More Advanced Scale Calculator
Let’s take the spreadsheet a stage further. This time we’re going to take a measurement in feet and convert it to millimetres, and then scale it. Set up the spreadsheet as shown in fig.4 and enter the following formula into cell E5:

The Formula we are using:

Fig.4

An explanation of the formula:
‘=’ means we’re entering a formula
‘(C3*12+E3)’ means we’re converting the feet entered in cell C3 to inches by multiplying by 12, and then adding on the inches entered in cell E3.
‘*25.4’ is the conversion factor to get from inches into millimetres.

Notice that when you select a cell that contains a formula, the formula also appears in the input line above the column letters. Formulae can also be entered here.

Now we can scale the measurement in millimetres using the method we used in the first example. Notice the formula for cell E9 in the input line. This spreadsheet shows us that 2 feet is equivalent to 9.53mm at 1:64 scale. (Fig.5)

Fig.5
 

Lets do a bit of tidying up. It’s unlikely that you’ll need to measure to the nearest hundredth of a millimetre, so the final output can be formatted to display to the nearest required figure, in this case tenths of a millimetre. Fig.6 shows the dialogue box for formatting numbers. Note the other tabs for other ways in which the cells can be formatted.

1. Right click the output cell and select ‘Format Cell’ from the context menu.
2. Click on the ‘Numbers’ tab
3. Change the number of ‘Decimal places’ to 1

Fig.6

NEXT PAGE