A Spinner’s Spreadsheet
by Jolie A. Elder
While spinning need not be heavily mathematical, in some situations math can be helpful. If you are math-averse, having these calculations in a reference spreadsheet can be a big help.
Disclosure: I have minimal skills at Excel. While my final spreadsheet is not the most elegant, it works. Also, Excel is very sensitive. When it wants “ (straight quotes), it does not want “ (smart quotes). If you are accustomed to word processing software that autocorrects and anticipates your needs, be forewarned that is not Excel. Excel is specific, accurate, and detailed, and expects the same from its users. Human beware!
Convert between yards and meters
Let’s start with a very simple calculation – converting between yards and meters. The conversion numbers are as follows:
1 yard = 0.9144 meters
1 meter = 1.0936133 yards
I start by writing “Length Converter” in cell A1. I use “Merge and Center” to give me a nice title. Then I write “yards” and “meters” in cells A2 and B2.
Since I want to convert from yards to meters, in cell B3 I type
=A3*0.9144
then tap the <return> key. I’ve told Excel to multiply the number in cell A3 by 0.9144, the conversion factor from yards to meters.
Type a number in cell A3 and tap <return>. Success! We can convert yards to meters.
For a double-check, I type “1” in cell A3. I should get “0.9144” in cell B3.
Upon further investigation, I discovered Excel has a convert function already in it! Sweet! The formula is:
=CONVERT(number,”yd”,”m”)
As an alternative, into cell B3 I can type the formula:
=CONVERT(A3,”yd”,”m”)
Then hit <return>. I don’t even need to know or understand the conversion factor.
If you are accustomed to translation software, you might expect to type a number into cell B3 and have Excel translate meters to yards in cell A3. Sadly, Excel does not work this way, unless you know how to program it in VBA. Since we would rather spin than write spreadsheets, let’s not go there. This is why we have friends who are professional engineers. (Thank you to my friend Larry Acuff for confirming that yes, you can upgrade this spreadsheet with VBA code.)
Since I’d like to convert in either direction, I’ll add a few more cells to my spreadsheet. Into cell A5 I write
=CONVERT(B5,”m”,”yd”)
Now I can put a measurement in meters into cell B5 and get the yard equivalent in cell A5.
You may notice rows 3 and 5 are not equivalent numbers. They are two completely separate equations. It would be nice to type either yards or meters into row 3 and have row 5 give me both answers. Can I do that?
This is where we go a wee bit past Excel basics. Time to use the IF function.
I delete all the data in cells A3, B3, A5, and B5. Bye, bye, formulas!
Here’s my thought process. If I enter yards in cell A3, then I want cell A5 to copy that information. But if I left cell A3 blank and instead entered meters in cell B3, then I want Excel to convert those meters to yards. Similarly, I want cell B5 to have meters, whether copied directly from cell B3 or calculated from yards entered in cell A3.
The magic formula for cell A5 is
=IF(TRUE=ISBLANK(A3),CONVERT(B3,”m”,”yd”),A3)
In human-speak that sentence means the following: If it is true that cell A3 is blank, then convert the number in cell B3 from meters to yards; otherwise, copy the number from cell A3.
A rewrite of that formula goes into cell B5
=IF(TRUE=ISBLANK(B3),CONVERT(A3,”yd”,”m”),B3)
Give it a try. I know 6 yards is a little less than 6 meters. What happens when I put 6 in cell A3 and tap <return>?
Cell B3 stays empty. But cell B5 now says 5.4864. That would be a little less than 6. The yard and meter equivalents are both on row 5. More success!
Try clearing A3 and putting a number in B3 instead. I still get yards and meters, but this time the yards were converted and the meters were copied.
Convert between ounces and grams
Wouldn’t it be nice to convert ounces and grams the same way?
I’ll repeat what I just did. I copy cells A1 through B5 and paste them over cells C1 through D5. I change the text to “Mass Converter,” “Ounces,” and “Grams.” Then I change the formulas. All I have to do is put “g” and “ozm” in place of “m” and “yd”. The formulas for C5 and D5 are as follows:
=IF(TRUE=ISBLANK(C3),CONVERT(D3,”g”,”ozm”),C3)
=IF(TRUE=ISBLANK(D3),CONVERT(C3,”ozm”,”g”),D3)
Try playing with it. In my example, I know it is typical for me to spin 100 grams but measure length in yards.
Grist
Since I now have both length and weight on row 5, I could do another calculation – grist.
Grist is a length of yarn per mass of yarn. It is usually expressed as yards per pound, but you could use meters per kilogram, or even bastard units such as yards per gram or meters per ounce. You could even try furlongs per stone or parsecs per solar mass. 😉
For my spreadsheet, I’ll make a grist calculator that gives me yards per pound, yards per kilogram, meters per pound, and meters per kilogram.
I write “Grist Calculator” in cell A6, then I merge and center cells A6 through D6. I write “yards” in cells A8 and B8, “meters” in cells C8 and D8, “pounds” in cells A10 and C10, and “kilograms” in cells B10 and D10.
I want to copy the appropriate measurements into my grist calculator. My yards measurement is in cell A5. I enter
=A5
into cells A7 and B7. For meters I enter
=B5
into cells C7 and D7.
I’ll need to do some conversions for pounds and kilograms. The pound cells are A9 and C9. I’m back to using the convert function:
=CONVERT(C5,”ozm”,”lbm”)
Finally, cells B9 and D9 convert grams to kilograms:
=CONVERT(D5,”g”,”kg”)
I have all the numbers where I need them. I just need to do some division.
Cell A11 is going to be yards per pound. I enter
=A7/A9
That gives me a number representing yards per pound. If I copy and paste cell A11 into cells B11, C11, and D11, Excel thoughtfully realizes the division I want. I now have grist expressed four different ways in row 11.
I could use row 12 to write “yds/lb,” “yds/kg,” “m/lb,” and “m/kg.” But if I want to be fancy, there are a couple things I can do. First, grist is rarely expressed in decimals. It is nearly always rounded to a whole number. Second, it would be nice to have the units in the box right next to the answer. I highlight A11. In the Format menu I choose “Cells…” or hit ⌘1. From “Number” I go to the bottom and choose “Custom”. In the box I type
0 “yds/lb”
and click <OK>.
Now A11 gives me a whole number in yds/lb!
Repeat this approach for cells B12, C12, and D12, giving each the correct units.
Other notes
At the end, I draw thick box borders around each section of the spreadsheet. I haven’t added background color to any cells, but I could do that if I wanted to clarify where to input data or to highlight the answers.
To use this spreadsheet, I merely empty all the cells in row 3.
There are some error messages. Excel does not like to divide by 0. And this spreadsheet is not idiot-proof. I should enter only one number in either A3 or B3, not both. Similarly, I enter only one number in C3 or D3, not both.
There are ways to refine this spreadsheet using more advanced skills in Excel. For example, there are ways to hide the zeros or prevent Excel from attempting to divide by zero. But for most of us, saving this as a spinner’s spreadsheet or a grist calculator will work just fine.
Jolie Elder has explored a wide range of needle arts after learning to cross stitch at age four. Her cleverest unvention is a method for working stockinette-based laces reversibly. You can view her experiments at jolieaelder.blogspot.com and the YouTube channel Jolie knits.
