How to: calculate sleeve shaping in Excel

I've been designing for a long time. Wait, that's wrong. I've been "designing". Since I started knitting back in high school, I've designed my own projects (usually sweaters) WAY more than following patterns. And that used to make me feel pretty smug and cocky, because clearly I knew my shit and if I could only remember to take decent notes, publishing patterns would be a doddle.

Of course when I actually started writing down those patterns, I realised just how wrong I was. It's not just the note taking (though my note taking really sucks). Or figuring out how to explain what I did clearly enough for anyone else to do the same thing without confusion (also harder than you might think, even when the actual project is pretty simple). No, the big thing that hadn't crossed my mind at all? Grading.

Writing a pattern for multiple sizes is a LOT more complicated than calculating just one. Little questions like the size of pattern repeats can rapidly become big questions. A process that used to start with taking my own measurements, calculating the total stitch count and finding stitches to fit that total? Um. Yes. That doesn't work so well. 

Plus, the way I used to perform certain calculations isn't practical for multiple sizes. As, for instance, spacing increases or decreases evenly along a sleeve. Doing one size at a time, this is not a big deal. Divide your row count by the number of increases needed and then fiddle around a bit with pencil and paper. But that's not particularly practical if you're writing a sweater pattern in six sizes.

While grading my latest design I realised that I simply didn't have a method for this. I've published a few patterns that required it, but I always futzed around with each size individually to get the numbers. This is RUBBISH. My grading spreadsheet is a marvel (for which I owe enormous thanks to Marnie McLean, Queen of Excel) and it automagically gives me everything I need – surely it should handle sleeves too?

But I couldn't find any resources to help me figure this out. There are various blog posts (and sections in the books on my knitting shelf) explaining how to calculate sleeve shaping for a single size, involving long division and diagonal lines connecting figures from those divisions and sketches and basically all the pencil futzing I was trying to avoid. None of it made much sense to me, and none of it was ready to just slap into Excel. I was feeling stupider by the minute. (This is a fairly common problem, since I mostly work late at night, when I'm shattered and can't concentrate, but when the kids are finally leaving me alone. Very likely if I were working 9 to 5, those posts would all have made perfect sense.)

So when I FINALLY figured out the logic of it from scratch – how to work it out, and how to ask Excel nicely – I was frankly ridiculously proud of myself, and also, I thought I'd better share what I learnt here. For the benefit of any desperate Googlers with the same questions, and probably for my own future benefit, when I come back to my spreadsheet template late at night a long time from now and don't remember why it makes sense.

(By the way, if your worry is spacing increases/decreases neatly along a single row? I can help you there too – see next week's blog post.)

so how to work it out?

Here we go. I'll explain the maths, and show my Excel workings. (My spreadsheet is not at all concise. You could probably cut out quite a few rows and put the calculations straight into a formula, but the longwinded way helps me to both see what I'm doing, and find errors quickly.)

For the purposes of this tutorial I'm going to assume you're knitting top-down sleeves, and refer always to decreases. Of course it works just as well for calculating increases from the bottom up. Note: if you're working flat, the numbers need a few tweaks – I'll provide an update shortly.  
Also, please see note at the end about checking your final row count – very important!

You need a few key figures to start with:
UA:  starting stitch count (upper arm circumference divided by stitch gauge)
CF: ending stitch count (cuff circumference divided by stitch gauge)
RND: total round count (sleeve length at underarm divided by round gauge)
DC: decrease count, ie UA minus CF – the number of stitches you have to get rid of.

Example numbers: 
UA = 91
CF = 39
RND = 165
DC = 91 - 39 = 52

1. We always work shaping at the beginning AND end of a shaping round, ie 2 stitches each time, so halve your decrease count to get the number of shaping rounds (DR). 

Excel formula: =DC/2
Example: 52/2 = 26

2. Now the fun starts. Divide RND by DR. Chances are it won't be a whole number, so round down. This gives you your basic decrease interval, ie, how many rounds between decreases (including the decrease round). Let's call it IV.

Excel formula: =ROUNDDOWN(RND/DR,0)  
Note: the 0 tells Excel to round down to zero decimal points.
Example: 165/26 = 6.3462, rounded down to 6

3. Normally you have some straight rounds at both the beginning AND end of your sleeve – you wouldn't work your decreases on the final round, you'd work an extra interval following the last decreases. So subtract IV from your total round count RND to get a smaller round count. I call that "available rounds", AR. (Another option is to split your interval in half for the start and finish – ie. work half of IV before your first decrease round, and half after the last one. This is useful if your numbers would then divide neatly, but since I'm calculating for a whole range of sizes, I assume they won't and add a full interval. Also, see VvNB note at end of this post.)

Excel formula: =RND–IV
Example: 165–6=159

4. Rounding down means you have a remainder (call it RM) – this is the number of rounds that would still need to be knit to make your sleeve long enough, if you just decreased every 6 rounds 26 times. Ask Excel nicely to tell you what's left over from dividing your available rounds by the number of decrease rounds.

Excel formula: =mod(AR,DR)
Example: 159/26 = 6 remainder 3

5. All the remainder means is that you have to spread an extra 3 rounds out across your standard decrease intervals – ie. add 1 round to 3 intervals: decrease every 7 rounds instead of every 6 rounds 3 times. So you have two separate decrease intervals, one smaller (IV) and one greater (GI). I usually put the longer intervals (higher round count) at the cuff end, but depending on the style of your sleeves, they could also go at the underarm.

6. Subtract the remainder from your decrease round count to get the number of times you need to repeat the smaller decrease interval. Call this number SD, for smaller decreases.

Excel formula: =DR–RM
Example: 26–3=23

7. The remainder is the number of times you need to work the greater interval. If you want to put this in its own Excel row – as I do, when spelling out my pattern instructions – GD=RM. 

You've got it all figured out! Your pattern instructions could look like this (with strings of numbers for each size replacing my abbreviations):

Work IV-1 rounds even. 
Next round (decrease round): K1, k2tog, work to 3 sts before end, ssk, k1.
Continue in pattern, repeating decrease round every IV rounds SD times, then every GI rounds GD times. Work IV rounds even.

Example, using my above numbers (and leaving out the decrease round details):

Decrease at each end every 6 rounds 23 times, then every 7 rounds 3 times. Work 6 rounds even.
Want to check the figures? 6 x 23 gives you 138 rounds; 7 x 3 = 21; add them, plus your extra 6 rounds, and you have 165 – just the right length.
You've worked decreases on each side of 23+3=26 rounds – that's 52 stitches. Just the right shape!

Vv NB final step: Do check your figures, especially the round count. It will occasionally happen that this method gives you too many rounds. (Why? Because of the fact that your decrease intervals are determined by dividing into your total round count, but the remainder is calculated by dividing after deducting one interval, as per Step 3. You can avoid this by choosing to make that last straight section a set number of rounds – say, 4 – or by splitting the interval as mentioned above; but will get a more even slope by using the full interval, and adjusting manually if need be.) If this happens, you'll find the original division result, before rounding down, is almost a whole number – that is, it almost divides perfectly. So forget the two-phase shaping entirely: use only the basic shaping interval (IV) and subtract a few rounds from the final (extra) straight section to adjust the length. 

Spreading increases or decreases evenly along a single row or round uses similar calculations but a different process, since you don't want to have all your extra "remainder" stitches bunched up on one side. I'll have a tutorial on how to do that next week.

Like this post? Sign up to my newsletter to get food for creative thought and updates on my work. Every two weeks. No spam.