Spreadsheets are one of the most efficient ways to calculate the numbers in your knitting pattern, as well as grade it into multiple sizes. They are more than just a table that you type your numbers into; they’re a powerful calculator that allows you to work out the numbers for one size, then at the drag of a mouse, do the same for the remaining sizes.

However, they can be very intimidating if you don’t have much experience with them! There are so many different formulas and functions available. How do you know what to do with them?!

In this blog post, I’m going to remove some of the mystery around spreadsheets and explain how to use four of the handiest functions that I use frequently in my pattern spreadsheets. Let’s get started!

What is a Spreadsheet Formula or Function?

Before I share the four most useful functions for knitting pattern spreadsheets, I want to take a moment to explain what I mean by formula and function.

A spreadsheet formula is a calculation that is made based on the information in your spreadsheet.

A spreadsheet function is essentially a formula that has been defined in advance, designed by the spreadsheet to carry out specific calculations using the data you give it.

CONVERT

Convert is a function that converts one unit of measurement into another, so it can be very useful when finalising your pattern’s finished measurements.

You should always provide finished measurements in both cm and inches to serve makers who use both the metric and imperial measurement system. I recommend calculating these measurements in cm first, before converting those measurements into inches. The CONVERT makes this process extremely easy.

Here’s how to use the CONVERT function to convert cm into inches:

=CONVERT(cell reference,“current unit of measurement”,“desired unit of measurement”)

So, if I had a number in cell B5 of my spreadsheet that was currently measured in cm, but I wanted to display the same measurement in inches too, I would use this formula:

=CONVERT(B5,“cm”,“in”)

MROUND

MROUND is a spreadsheet function that rounds a number to the nearest multiple of another, and this is one that I use all the time!

MROUND for Finished Measurements

Like CONVERT, this function is great for finished measurements. These measurements shouldn’t have multiple decimal places. Instead, I recommend that you round any measurements in cm to the nearest multiple of 0.5 and measurements in inches to the nearest multiple of 0.25.

For this purpose, you would use the MROUND function like this:

=MROUND(cell reference of decimal number,nearest multiple you want to round it to)

As an example, if I had a number in cell E9 that was measured in cm, but had multiple decimal places, I would use this formula to round it to the nearest half cm:

=MROUND(E9,0.5)

If I had a number in cell F1 that was measured in inches but had multiple decimal places, I would use this formula to round it to the nearest quarter inch:

=MROUND(F1,0.25)

MROUND for Stitch & Row Counts

You can also use this function when finalising stitch or row counts, especially when you’re working with a pattern repeat.

You just take the preliminary stitch count (the one based on your gauge) and then tell the spreadsheet to round it to the nearest multiple of your pattern repeat’s stitch count. This allows you to knit to a measurement close to your desired measurement whilst also accommodating your stitch count.

For this purpose, you would use the MROUND function like this:

=MROUND(preliminary stitch count,number of stitches in pattern repeat)

If I was working with a 4-stitch pattern repeat and my stitch count in cell C7 didn’t accommodate the stitch repeat, I could use this formula to update the stitch count:

=MROUND(C7,4)

Writing Formulas Inside the MROUND Function

You don’t just have to use cell references when using the MROUND function. You can actually write your formulas inside of it, as demonstrated below:

=MROUND(formula,nearest multiple you want to round it to)

For example, if I wanted to use the formula =(B36*2)+B37 in my spreadsheet, but I wanted to round the result to the nearest multiple of 2, I would use this formula:

=MROUND((B36*2)+B37,2)

This can considerably speed up the spreadsheet process as you are able to perform two calculations at once!

ROUNDDOWN

ROUNDDOWN is a spreadsheet function that rounds a number down, whether that’s to the next whole number or to a certain number of decimal places, which makes it useful when you are centring pattern repeats.

When centring a repeat, it is helpful to know how many full repeats fit within the stitch count. In order to calculate the number of full repeats that fit, you just divide the overall stitch count by the number of stitches in the repeat, rounding the result down to the nearest whole number.

For this purpose, you would use the ROUNDDOWN function like this:

=ROUNDDOWN(Stitch count/number of stitches in the repeat,0)

As an example, if I wanted to work out how many full repeats of my 6 stitch pattern repeat fit into my stitch count of 34 sts, I would use this formula:

=ROUNDDOWN(34/6,0)

In case you’re wondering, the 0 in this formula is telling the spreadsheet to round the number down to zero decimal places. If for another purpose, you needed a result with 1 or more decimal places, you can just replace the 0 with 1, 2, etc.

MOD

MOD is a spreadsheet function that tells you the remainder when you divide a number by another number, which is, again, very useful when centring a pattern repeat.

Using this function, I can find out the number of stitches leftover in my stitch count that don’t fit into a full stitch repeat. I can then halve the leftover stitches evenly to find out how many stitches to work in a partial stitch repeat at each end before working the full stitch repeat.

For this purpose, you would use the MOD function like this:

=MOD(Stitch count,number of stitches in the repeat)

So, if I wanted to work out how many stitches are leftover in my stitch count of 34 that don’t fit into a full stitch repeat of 6, I would use this formula:

=MOD(34,6)

Learn By Doing

I completely understand that for those of you who are unfamiliar with spreadsheets, a lot of this is going to feel like complete jargon, however, spreadsheets are one of those things that make more sense once you’re actually working with them.

If you’re interested in learning more about how I use spreadsheets, I’ve written a mini-series all about how to grade knitting patterns using a spreadsheet. I also teach some extremely in-depth lessons about pattern spreadsheets in Sweater Design School.

I highly recommend that you try out these formulas inside your next pattern spreadsheet and see how much time and effort they save you! You may even find more uses for them than the ones I listed here. I’m always discovering new ways to optimise my spreadsheets.

Got Any Top Tips?

Do you use spreadsheets for your knitting patterns? If so, do you have a favourite function that you use frequently? Share your experiences with your fellow designers in the comments section below.