This blog has moved


« Excel for Pattern Writing - Part III | Main | It's the final countdown »

Excel for Pattern Writing - Part III

Most pictures may be clicked to enlarge.

This tutorial picks up where the last tutorial left off and uses the formulas found in this spreadsheet. This tutorial can stand alone but may reference Excel skills covered more fully in previous Excel tutorials. If you haven't used the program much, you may want to review these other tutorials.

NOTE: The attached spreadsheet is just a sample and contains measurements that may be useful but which may not meet the standards for some publications. You are welcome to use what I have for your own design purposes but it'll be you who has to ultimately support any patterns written from it, so do your research first.

Special notes:
I've used Ysolda's measurement chart as a reference for many of the basic sloper measurements.
I'm using Amy O'Neill Houck's tutorial on estimating yardage to create my yardage estimation formulas.

The goal

In the last tutorial, we learned about

  • Rounding
    • Round to a whole number
    • Round to the nearest Even or Odd number or Round, Round Up or Round Down
    • Round to a multiple
    • Round to a multiple plus

In this tutorial, we'll cover

In the final tutorial, we'll cover

  • Calculating finished garment measurements
  • Calculating yardage based on a sample
  • Putting your numbers into MS Word

IF Statements

IF statements are hugely powerful. They allow you to do certain calculations only if particular criteria are met. For instance, if you look at row 73 of the spreadsheet, you'll see that the BO for the center of the neck indicates an odd number of stitches if the cast on was odd and an even number of stitches if the cast on was even. This way, we can be sure that both shoulders have the same number of stitches.

I can't possibly cover everything you can do with IF statements, but, in essence, you can indicate any number of parameters and corresponding results using, basically any number of formulas. Let's take a look at line 73, column M.

=IF((ISEVEN(M67)), (EVEN(C73)),(ODD(C73)))

Scary, huh? I swear it only seems complicated. IF statements, in their simplest form, have three parts.

=IF(test, true, false)

First, is the test. In this case: "is a particular value even?"
The next section is the formula to use if the value is true. In this case, we are rounding a particular value to the closest even number.
The last part tells us what to do if the test comes back false. In this case, we round the value to the closest odd number.

Formula element Reason
= Indicates a formula
IF( )

IF indicates the start of an IF statement.

The entire IF statement must be contained within parenthesis, in which more parenthesis may be necessary.


This statement determines whether a particular cell value or formula result is an even number. Note that the entire IsEven portion is in parenthesis as is the value you are evaluating.

Insert a comma after this formula. It indicates the completion of the value you are checking in the IF statement.


This portion of the formula determines what happens if the test comes back true.

In this case, we will round a particular value to an even number.

A comma indicates the end of this portion of the formula


The last part of the formula determines what happens if the value being checked is false.

In this case if the first value is not even then we are rounding a specific value to and odd number.

IF statements are going to help us complete the next section, which I hope will be one of the most useful things you'll get out of this tutorial.

Shaping intervals

Evenly spacing shaping intervals has been a real pain in the tush for me. While Maggie Reghetti offers a series of formulas for evenly spacing shaping, I'll tell you that my eyes glaze over every time I read that section. Conceptually, it just didn't make sense to me. After writing several patterns where I really struggled to formulate shaping intervals in a reliable and quick way, I finally came up with a set of formulas that seem to work. These formulas work whether you are increasing or decreasing and can be used for armscye, necklines, body shaping and any other area where you need to work shaping, evenly over a set number of rows or stitches.

With both of these formulas, you first must determine how many times you need to increase/decrease and over how many rows, or stitches, as the case may be.

Every X rows Y times

In some cases, shaping works out perfectly with, say, 4 decreases over 40 rows. In that case, you'll decrease every 10 rows 4 times. But, unfortunately, it doesn't always work out that way. In those cases, you'll need to decrease every X rows Y times and then every X+1 rows, Z times. This formula is great for working decreases from the hem to the waist, where you want the last decrease to fall at the end of the section. If you need to center the shaping within a set number of rows, you can just split the very first interval between the start and end of the section. No additional complex math is needed.

The formula has 4 lines.

You can follow along starting at row 47, column M

M47 =ROUNDDOWN((M46/M45),0)

First, we divide the number of decreases into the number of rows and round down. Why do we round down? Because we need a whole number and if we just round, it might round up which would result in too many rows being used in the shaping formula.

Formula element Reason
= Indicates a formula

Indicates that we are rounding down the value or formula contained within the parenthesis.

(value1/value2), We are dividing the number of rows (value1) by the number of decreases (value2.) Since it's a formula, it must be contained within parenthesis. Place a comma after the value to be rounded.

0 indicates that the number should be rounded to a whole number.

Next we have to determine how many time we'll decrease every 5th row.

M48 =M45-(MOD(M46,M45))

MOD gives us the remainder when one number is divided into another. For instance, if you divide 3 into 13, you get 4 with a remainder of 1.

This formula is saying that when we divide 7 into 40, we get 5 with a remainder of 5. Since we have 7 decreases total, we know that 5 of those decreases will need an additional row added to them, to eat up those remainders.

Formula element Reason
= Indicates a formula
value-( )

We are subtracting the number of remainder stitches from the total number of shaping intervals required. The parenthesis ensure that we subtract the value of the full formula contained within the parenthesis.

MOD(value1, value2)

Delivers the remainder, when one number is divided into another.

Value1 is the value to be divided. Value2 is the value by which you want to divide (divisor).

Either of these values can be a formula. That formula must be contained within another set of parenthesis.

Let's skip down to cell M50. We'll come back up to M49 in a moment.

M50 =M45-M48

In this line, we are subtracting the number of intervals in the first section from the total number of intervals required for this section. We need 7 decreases and have used 2 decreases in the first section, leaving us with 5 remaining decreases to work. If the number of decreases worked evenly into the number of rows, we'd get zero here, otherwise we should have some other whole number.

Back on up to M49. Remember our IF statements from above? Well here is one in the wild. Be quiet, they scare easily.

M49 =IF(M50=0,0,(M47+1))

Here we are checking to see if the value in M50 is 0, if so, we don't need to do anything else, so our IF statement is saying that if it is true that the value below is 0, then just put a 0 in this box as well. If the value of the cell below is NOT zero, however, we are going to take our original interval value of 5 and add 1.

Formula element Reason
= Indicates a formula
IF(value1, value2, value2)

Standard IF statement. Value1 is the value to check (is the number below equal to zero?), value2 tells us what to do if the result is true (insert a zero into this cell,) value3 tells us what to do if the value is false (add one to the number of rows in the first shaping interval section.)

How does all this work out? You can visualize it this way.

Decrease 7 times over 40 rows

40 / 7 = 5 with a remainder of 5
7 sets of 5 row increments 5 5 5 5 5 5 5
Plus 5 remainder stitches     1 1 1 1 1
Dec every 5 rows 2 times 5 5          
And every 6 rows 5 times     6 6 6 6 6

It sounds complicated, but it works and once you've set up a template, you can easily plug in your number of shaping intervals and number of rows or stitches over which to shape and quickly get your results.

Next and every X rows Y times

A slight variation of this theme is to decrease on the next row and then at regular intervals. The formula is identical except that one row (or stitch) and one decrease (or increase) are subtracted to account for the "on next" instance.

The formulas are largely the same, so I won't break each one down in the same detail, but I have highlighted the areas that differ in red.

M58 =ROUNDDOWN((((M57-1)/(M56-1))),0)

M59 =(M56-1)-(MOD((M57-1),(M56-1)))

M60 =IF(M61=0,0,(M58+1))

M61 =(M56-1)-M59

I use this formula whenever I want the first and last row (or stitch) of a section to be an increase or decrease. I use this when I am working dart shaping on a garment. The first and last row of the dart are at set points and I want shaping to happen in both places. This formula delivers this sort of interval for me.

You might also use this if your need to change the number of stitches in a row to maintain a set width with different stitch patterns. For instance, if you go from stockinette to lace, you will need fewer stitches in the lace section to maintain the same width. Simply calculate how many stitches per inch (or centimeter) in each stitch pattern, multiply by the number of inches (or centimeters) wide, the row should be and subtract the smaller number from the larger number. This tells you how many intervals you have. The number of stitches in the stockinette area is the length over which you need to work those intervals.

In conclusion

This tutorial is probably the driest of the bunch but I hope the info within proves useful enough to offset the boredom. When trying to write a pattern in 8, 10, 12 or more sizes, determining the number of shaping intervals for each can be mighty tedious if you don't have a quick formula. With this information, you can simply input how many shaping points over how many rows or stitches and quickly get a result. This can be as useful for determining how many decreases to do in a yoke or evenly space increases when changing gauge, mid project, as it is for standard body, neck and arm shaping. I think you'll find a lot of uses for it.

When next we wax tutorially, we'll talk about calculating the finished garment measurements, estimating yardage based on your sample's yardage, and inputting your excel numbers into a Word document for use in a final pattern.

As always, these tutorials are brought to you not as a definitive resource but simply as a set of suggestions. I welcome feedback on other approaches and suggestions for future tutorials.


Share this post

Comments (6)


This is all really helpful, thank you!

Thank you for sharing yet another timesaving tidbit of designing with the computer! You are amazing to have put this all together! :)

More WOW! I'm speechless -even in Spanish.


wow again.


Wow! Thank you so much for taking the time to put these tutorials together. As someone who is just getting started designing, I can't tell you how much I appreciate it. It's really hard to find out the pros go about it. Thanks again!

I really appreciate you sharing your designing techniques with us in these thoughtful tutorials. I'm sure many of us will find it extremely useful and timesaving. Thanks.


This page contains a single entry from the blog posted on May 29, 2008 4:56 PM.

The previous post in this blog was Excel for Pattern Writing - Part III.

The next post in this blog is It's the final countdown.


Site Info

Powered by
Movable Type Pro 5.14-en