Tension square main

Midweek Masterclass: Using Excel to change tension

Want to adapt your stitch counts for a different yarn or tension? Ellen Gill explains how Excel can do the sums for you quickly in this week's Midweek Masterclass.

12th October 2016

Are you a knitter who is willing to make your own changes to pattern instructions? Do you know the basics of Excel, but haven’t really put it to good use yet? In this issue’s masterclass, we show you how the spreadsheet software package Excel can help you take on the task of knitting a project with an entirely different tension from the original pattern. Don’t worry: for this masterclass, we’ve done the maths so you don’t have to!

Tension square

Don’t have access to Excel? There are free alternatives available, such as Google Docs Spreadsheet or OpenOffice Calc. Although these aren’t quite the same as Excel, they’re similar enough that everything in this masterclass will still apply.

Getting set up

There are many reasons why you might use a different tension for your project from that stated in the pattern: you knit to a different tension or are using a different yarn; maybe you want to add or remove motifs… the list could go on. But the fact remains, if you don’t have the same tension as the pattern, you will not be able to reach the stated pattern dimensions by following the printed instructions.

Enter Excel! While it can seem like an inscrutable black box to the unfamiliar user, what it really does best is automate large amounts of repetitive maths. Exactly the kind of maths you would have to do when figuring out a tension change, in fact.

As an example we’re going to take a pattern from this issue, ‘Mint Tea’, and convert the smallest size to work with a 4ply-weight yarn, instead of a DK.

Step 1: Work out your new tension

First things first: you must knit your new swatch in the yarn or to the tension you want to work with. This will be the fabric your project is made from, and must be large enough to accurately measure your new tension from. The standard size is 10cm x 10cm (4in x 4in), but if your project uses a complex motif or texture, make sure your swatch contains at least one pattern repeat, plus enough extra on all sides that the motif isn’t distorted at the edges. Be sure to wash your swatch in the same way you will wash your final project, and let it dry before taking measurements.

The original pattern tension for ‘Mint Tea’ is 22 sts and 30 rows to 10cm square, and we’ll need to include these details in our spreadsheet. Our new tension is 28 sts and 35 rows and we’ll put them next to each other in a neat table, here (1). (It’s not necessary to use colours, but it helps!)

Excel1

Step 2: Entering the pattern into Excel

Next, enter the original pattern stitch and row counts wherever a change is needed. The pattern schematic helps us see the main measurements that have to stay the same regardless of tension (2).

Excel2

As this is a relatively simple batwing top, we mainly need to make sure that the stitch counts meet the schematics up to the armpits, and that the armholes have the right amounts of rows. At some points in the pattern we see instructions like “continue until work measures x cm”. As that’s a fixed measurement, we don’t need to include it in the spreadsheet.

We also haven’t included how many repeats of the chart to work. As the original stitch counts are roughly double the stitches used in the chart, we will see what our new cast-on looks like and decide how many chart repeats to fit into that.

We’ve used the column on the right to note the instructions that go along with each number so that we don’t lose our place (3).

Excel3

Step 3: The maths bit

To get the new stitch counts, we need to divide the old stitch count by the old tension, then multiply it by the new tension for every single row we’ve created. On paper, this would take a while, but in Excel we need to type in the maths just once.

Excel has its own kind of code for maths. The program’s ‘Help’ file is a wealth of information, but there are actually only a few things you need to know to get started.

First, when typing a formula into a cell, always start by typing = . This tells the program that what you’re typing is indeed a mathematical formula. The following symbols are also used:

+ means plus

- means minus

* means multiply

/ means divide

Excel also uses brackets to indicate which bits of the formula should be calculated first, as separate sums.

Next, every cell in your spreadsheet has a ‘coordinate’ you can use in formulas. It’s simply the letter of the column that cell is in, followed by the number of the row it’s in. We refer to this as a cell reference.

So our formula for the first ‘new stitches’ box should look like this:

=(old stitch count/old tension)*new tension

Now, you could manually type in the old stitch count and the old tension, but here we’re going to show you how to link the two together using the cell references.

When you’re copying the same formula into lots of cells, Excel usually changes the cell references so they always point to cells the same number of rows and columns away from the cell you put your formula into. But we don’t want the gauge references to change!

You can tell Excel to keep cell references fixed simply by putting a $ sign in front of them.

$A1: only the column is fixed.

A$1: only the row is fixed.

$A$1: the entire cell reference is fixed.

This is how your final formula will look in Excel:

 =(old stitch count/$old tension)*$new tension

We want to enter this formula for the row count column as well, and that formula will look like this:

=(old row count/$old tension)* $new tension

In cell J6 of the example spreadsheet that’s =(G7/$C$5)*$D$5 (4)

Excel4

Step 4: The magic bit

So far, we’ve done a lot of setting up. Now here comes the part where Excel shines. Select both of the new stitch and row formula cells and move the mouse to the bottom right corner of the selection until the cursor turns into a black cross, as shown in (5).

Excel5

Now click and drag to the bottom of your column. Voilà! The formulas have been copied the whole way down. Our tension references have stayed fixed, and the stitch counts references have kept every calculation on the right row (6).

Excel6

Tip: You can choose whether Excel actually shows a zero in cells with zero value by going to the Windows menu:

File > Options > Advanced > Display Options for this Worksheet

or on a Mac:

Excel / Preferences / Zero Values

and unticking the corresponding box. We’ve unticked it here because otherwise the Rows column would be full of zeros and look messy.

Step 5: Applying rounding functions to your initial output

As you can tell from the last picture, the conversions won’t all be smooth. You may well find your new stitch counts include fractions, such as in 7. If you know how to knit half a stitch we’d love to hear about it!

Excel7

If you were doing this with pen and paper you’d have no choice but to go through and round everything as you go. If you still prefer your own judgement, it’s simple to enter your own numbers based on the Excel-generated fractions. But Excel comes with its own rounding abilities, which you might like to consider.

Simple rounding:

Select the new stitch count column and click the right of these two buttons: This changes the amount of decimal places Excel displays, so keep clicking until all decimal points have disappeared from the screen. Easy!

Less Simple rounding:

If you have a taste for something more involved, Excel has a number of functions that automatically round numbers in various ways. (A ‘function’ is the Excel term for a command). We’ve created a third column for rounded stitch counts, which helps keep track of things.

=rouND(new stitch count,0)

This function has two fields in the brackets, separated by a comma. The first field is the number you want rounded, and the second is how many decimal places you want that number to be rounded to. The first field is your fractional stitch count, and we want a whole number, so the second field is zero. In our spreadsheet, it looks like this (8).

Excel8

Simple variations on round

=rouNDuP(new stitch count,0)

=rouNDDoWN(new stitch count,0)

ROUNDUP rounds your stitch count up to the next whole number, and ROUNDDOWN rounds down to the next whole number. You might prefer to use ROUNDUP when you know the pattern measurements run a bit small for you, and down for the opposite.

=ODD(new stitch count,0),

EVEN(new stitch count,0)

Finally, these two functions round your stitch count to the closest odd and even whole numbers respectively. These are useful if you know your project needs to be perfectly symmetrical, or is symmetrical around a centre stitch.

We don’t need to use all of these in our example, but why not play around with them and see how they can change your results? Functions are useful to know and can be incredibly powerful problem-solving tools.

Crucially, the two fields in these functions can be formulas as well as cell references and plain numbers. That allows you to pull off tricks such as the following…

Advanced number crunching

What if your knitted fabric has an all-over pattern motif that requires a specific number of stitches to start with? What if that number of stitches comes in the common-but-awkward format of e.g. “a multiple of x + y”?

You’d need to take quite a few steps to work out just what number of stitches closely fits your pattern schematic and your pattern motifs. Or, you can just type this into an Excel cell:

=ROUND((new stitch count-y)/x,0)*x+y

This formula looks complicated, but it’s just the same maths that any designer would use when writing a knitting pattern. The benefit of turning it into an Excel formula is that anyone can plug it in and use it without having to think too hard.

Again, in this case, switching ROUND to ROUNDUP always rounds it upwards from your original stitch count, and ROUNDDOWN always rounds it down.

You're ready to knit!

By linking all these different cells together step by step, you can see that we’ve built a pretty sophisticated knitting calculator. You can change the tension to whatever you like and instantly generate a whole new set of numbers.

If you’re interested in learning more about Excel, there are lots of resources on the internet. Marnie MacLean’s tutorials (http://marniemaclean.com/blog/excel/) focus on how to set up Excel for designers, but they’re a great insight into the kind of things you can do.

For those on Ravelry, the designer’s group has a long-running topic thread on various Excel tricks (http://bit.ly/ravexcel). There’s even a dedicated Ravelry group called Excel Geeks (www.ravelry.com/ groups/excel-geeks).

We’ve only covered a very small amount of what you can do with Excel, but hopefully we’ve given you an idea of how to speed up knitting maths in future!

Meet the designer

What did you think of this week's Midweek Masterclass? Let us know on Facebook or Twitter! Missed a Masterclass? Don't worry! Simply click here to find all of our classes so far.

Subscribe to The Knitter

The Knitter is the premier magazine for confident knitters who are looking for beautiful, original patterns from the world’s best designers. Subscribe today and receive 3 issues for £5 - saving 67% on the shop price!

The Knitter issue 90 cover