Wednesday, March 1, 2017

Estimating shawl yardage requirements using Excel

My Redbud Winter shawl (coming March 2!) is knit from the top-down in a decreasing triangle. I thought a tutorial on estimating your yardage might be useful. You do need to know how to use Excel for this method. I’m sure it could all be done by hand but it makes my head hurt to think about it :D

You will need
Excel (or another spreadsheet program)
Scale that will measure in grams

Step 1
Knit a swatch. Yep. Sorry but you need this information! You need to count the total number of stitches in your swatch and then weigh it. (If you use anything other than a backwards-loop cast-on, count the cast-on stitches twice. Don’t forget to count the bind-off. So if you cast on 20 stitches and knit 20 rows, and then bind off, your total number of stitches is 20 (stitches) x 20 (rows) + 40 (cast-on) + 20 (bind-off) = 460.

Step 2
Weigh your swatch. Let’s say it weighs 2 grams (I am totally making these numbers up!)

Step 3
Calculate your stitches per gram: total stitches/grams = 460/2 = 230

Step 4
Determine your cast-on by multiplying your stitch gauge per inch by the wingspan you want. Let’s say 5 stitches per inch and 50 inches for our example. So my cast-on number would be 250.

Step 5
Set up your spreadsheet with the following columns:
Row number|row stitch count|cumulative stitch count
The first line will be your cast-on, then number the rows below. (Don’t worry yet about how many rows to add, just do what you can see on the page.)
The first stitch count entry will have your cast-on number calculated in step 4.
The first cumulative stitch count will be 2 times the cast-on number (for a long-tail cast-on). Use a formula here so it will adjust if you change your stitch count.

Step 6
Set up line 2. For this pattern you are decreasing one stitch on every row, down to 1 stitch (there are actually 2 rows where you decrease 3 stitches, but there’s no need to complicate things by trying to be that exact here. A few extra rows will just give you a bit of a cushion which is a good thing :)

Your cumulative count will be the count in the box above plus the current row count.

Step 7
Select the two boxes you just set-up and drag them down the screen.

Step 8
Select your last row of data and drag it down the screen until you get a row count of 1 – if you overshoot, just delete the extra rows.

This means we will be knitting about 31,615 stitches which will take 137.5 g. (31615/stitches per gram calculated in step 3)

What if I find out I need more yarn than I have? Let’s say you only had 125g. That means the total stitches you can expect to knit is 125 x 230 = 28750. Changing the cast-on number to 240 gives me a total stitch count of 29160. Almost! 

Let’s try 238 (it has to be an even number for this pattern) –

That works!

It's a good idea to check your yardage as you go - numbers you get after knitting a lot of stitches are more accurate than what you get from a swatch. And if you don't want to swatch, you can at least determine if you can keep going or if you need to bail. Determine the amount of yarn you used and calculate your stitches per gram. You can then multiply your total grams available by the stitches per gram and see if you need to rip and restart with a smaller cast-on.

Checking as you go
Name the final stitch total by clicking on that box and entering a name in the spot circled in red. I used “totsts”

Add three more columns – ‘g used’, ‘% knit,’ and ‘% used.’

The % knit is the cumulative total for that row divided by the total stitches to knit –

You need one more field to calculate the % used – grams available.
Name that field – I used totgs

The calculation for the % used will be the total yarn used at that point divided by the total available.

Select the two fields we just created and change them to percentages. 

Drag those two field to the end of your rows.

If it doesn't go to 100% at the end, you did something wrong!

After knitting any row, weigh your yarn to determine how much you’ve used (not how much is left! Subtract what's left from what you started with) and enter the grams in the ‘g used’ column. Let’s say I’ve used 4 g through row 2. 

This means I am okay. If your percent used is higher than your percent knit, you may have a problem!

This is the method I use all the time when I’m designing a shawl and need to make sure I’m not going to run short. Different patterns will have different rates of decrease or may increase instead, but the basic principle is the same. You could also use this method to see how big of a shawl you can knit using the Dogwood Spring pattern (also coming March 2) with your yarn and gauge. You would start with 1 stitch and increase each row in the spreadsheet for that one. Once you know your possible final row and stitch count, you can estimate your finished size based on your gauge.

Hope you find it helpful!


  1. Thanks! This is so useful. I just made my own spreadsheet for a bottom up shawl now and at least got an idea as to how many grams of yarn I'll need :)

  2. Oh, wow. Thank you for this! I got pointed to this tutorial by helpful people on Ravelry, and it's exactly the guide I needed to help figure out my own spreadsheets.

    Thanks again!