Using a Spreadsheet to Produce Scaling Charts
Kim Holland

 
All models are built to a scale, and to assist in the conversion of the scale to the dimensions in use I create “scaling charts” using a computer, because its easier, but they can be created by hand, and that takes a bit longer. This article does assume some familiarity with computer use and assumes that you have used Microsoft® ‘Excel’© previously. If not refer to http://modelshipworld.com/phpBB2/spreadone.php which is an introductory article on what use can be made of computers to assist us model boat builders. There are plenty of other articles which refer to computer use in converting scale measurement, they are worth finding, all can be very helpful.

Author's 1:48 HMV Bounty
 
Scale is normally given as 1:48 or 1:64 etc and in imperial measurement sizes this equates to ¼ inch to a foot for 1:48. To save having to convert dimensions each time I create a chart, reading feet and inches off the chart to give the scale size direct in mm. Similar charts can be created for metric work, but I deal with 17, 18 and early 19 Century shipping mostly, so imperial is paramount to me.

The computer spreadsheet ‘Excel’ can do all this quite rapidly, and I produce two charts for each model, one for the main dimensions of length, and one for the rigging converting imperial rope sizes in circumference to metric scale sizes in mm of diameter. This is very handy when selecting threads and cords because the metric diameter is often quoted, and can easily be verified by direct measurement and then converted back to the imperial equivalent so comparison with other ropes can be made to keep everything in scale. Nothing looks worse than to see 15” shrouds and then say halyards of about half their visual size used, making them about 8” rope when 3” should have been used.

Creating the scale dimensions of length chart.  

(All references in bold (A1, B6, G23) are the spreadsheet cell reference codes.)

Basic layout.

Select a new worksheet in Excel, (figure 1.)

Leave A1 blank, this is where the scale number is entered, 48 for a 1:48 scale.

Enter the word “feet” in A3, and then 0 through to 11 in B3 across to M3. These are the inches.

Enter numbers 0 through to 30 in A4 down to A34 (or higher its your chart). These are feet.

To make the chart easier to read insert lines every 5 rows down and every 3 columns across. Then put a border around the whole chart. Border the scales across and down as well.

Make the entries in column A and row 3 “bold” format.

Enter mm in B4, a reminder that the chart figures are in millimetres; then select all the columns in use and centre them all (figure 2).

 
 
The Formulae

The formula used make one assumption; 1 inch is equivalent to 25.4mm in length. If you wish to use a more exact conversion figure, do so, but note that the table values returned are rounded to the nearest 0.1mm, as I assume we cannot or do not require to be any more exact. If you want, the cells can be formatted to return to any number of decimal places, but I would question whether anyone can cut scale model items any better than 0.1mm with equipment with blades over 0.25mm thick.
The formulae I derived adds values to the preceding cell value.
 

Enter =((($A4*12)+C$3)*25.4)/$A$1 into C4. The value displayed should be #DIV/0! which is an error code, do not worry (figure 3)
 
Enter, to check the formula only, the value 12 into A1 the value displayed in C4 should now be 2.116667. This is 1/12 of 25.4. The value 12 is from the scale 1:12. (figure 4)

 
Next copy the formula in C4 and paste into each cell in the row across to M4, which should have the heading 11. Each cell should now display increasing values across the table, the value in M4 being 23.28333. (figure 5)
 
Now copy the formula from C4 into all the cells B5 to M34. Use the paste special function and paste the formula only. This preserves the other formatting of the cells already done. Cell M34 should have the value 785.2833 displayed. (figure 6).
 
To get all values rounded to 0.1mm, select all the cells B4 to M34, and using the format cells tab, select number and then set the decimal place to 1, click ok. (figure 7).
 
If it all works, save it. Then copy to make several more worksheet in the workbook, each one at a different scale. I head each chart with a reminder heading of the scale, its imperial equivalent and label the tab with the scale as well. Any scale and range of dimensions can be set.
 

Creating the scale rope sizes chart.

Basic layout
On a new worksheet in Excel, merge A1 and B1 together, then merge C1 through to N1 as well. These form the heading blocks, “ACTUAL circumference” and “scale [all values in mm].” (figure 8.)
 

Row 2 is labelled:
A2 “inches”
B2 “mm”;
C2 “1:12”
D2 “dia”
E2 “1:24”
F2 “dia”
G2 “1:36”
H2 “dia”
I2 “1:48”
J2 “dia”
K2 “1;64”
L2 “dia”
M2 “1:96”
N2 “dia”

Column A values, start from 0.5 in A3 up to 12.0 in A27. Again these can be altered to suite, (figure 9)
 

The Formulae

Column B computes mm values from the A values (assuming 1 inch is equivalent to 25.4mm).
Enter the formula “=A3*25.4” in B3. Copy it to all the cells in column B in use, and round the decimal place to 1 place. (figure 10.)
 
Enter the formula “=$B3/12” in C3. Copy it to all the cells in column C in use. (figure 11.)
 
Enter similar formulae to columns E, G, I, K and M changing the divisor value to the scale value of the heading in row 2. (figure 12)

 

Now, using geometry, the circumference of a circle is given by the formula 2p r where p is a constant called pi, with an assigned value of 3.1415927…… and r is the radius and the radius is half the diameter. So the diameter is twice the value of the circumference divided by 2 x pi (p ).

Enter the formula:
=2*(C3/(2*3.1415927)”
to D3 (figure 13.) Copy it down to all cells in column D. Also copy the formula to F3. The formula changes from (C3/ to (E3/ to (G3/ as it is pasted to the target cell.
Repeat the above for columns H, J, L and N making the appropriate formula changes.
 
As this was created, I thought that with modern rope sizes being measured as diameter in both metric and imperial (and this being an easier measurement to physically make), I have added two columns for rope diameter as columns O and P. (figure 14.)

Enter the formula “=2*(A3/(2*3.1415927)” in O3 and copy to the rest of the column O. Then enter the formula “=O3*25.4” to P3 and again copy to all cells of column P.

For this chart I rounded all ropes circumference sizes to two decimal places in inches, one decimal place in mm and all calculated diameters within the chart to two decimal places (to show the very small differences). The last two columns O and P, are formatted the same decimal places as columns A and B.

Finally, so that the whole chart is on screen, column widths C through to N were changed to be 7.43, the red boxed area on figure 14.
 
 

 

 

Measuring the size of model threads.
A quick note on measuring threads for scale rope size. This is achieved very simply by winding loosely 10 turns of the thread about a metric ruler. Close up the turns so they are together but not squashed, and take the length reading, divide this by ten and that is the average thread thickness as the diameter. Find this value (or nearest) on the rope chart for the scale of vessel being made and that reads off the imperial circumference rope that that particular thread represents. Its that simple. In later articles I’ll show you how to make your own rope too, to the size you require.

Figure 15, the rope measures 10mm for 10 turns, therefore 1mm rope diameter, at 1:48 scale, a 2inch rope.