## Sunday, 5 April 2009

### One of the joys of being a scientist is that we learn skills which are applicable in other facets of our lives.

Unlike some people who were drawn to science, I am not a seriously math-oriented person. Sure, I took the algebra and calculus required to obtain my Bachelor of Science degree, but I didn’t feel the need to be doing calculations of my own on a daily basis. This was one of the reasons which drew me into Geology—it is a science, but there are many disciplines in geology wherein math isn’t needed often. For my Master’s degree the only calculations I needed to make were all done in a single afternoon. I needed to convert from feet/miles to meters/kilometers since my base map was in feet (my advisor and I both felt that metric is the more appropriate system in which to report results). This conversion is taught in pretty much all first-year geology laboratory courses in the US, and not only had I taken such a class, I was teaching that class that year, so I had the formula immediately to hand. I also needed to calculate apparent dip (what angle the tilted layer of a fold in the rock will appear to have if you happen to slice through it at some angle other than exactly perpendicular to it). This is taught in structural geology laboratory courses, and again, I happened to be teaching that class that semester, so had the necessary formulas at the ready.

For my PhD project, I am needing to do a fair few more calculations, on a much more regular basis. However, I don’t actually do the arithmetic myself; instead I set up Excel Spreadsheets with formulas as I need them, and then type a number into the appropriate cell, and read the answer from another cell. This is a very, very useful skill to have. It requires care to get the formulas typed in correctly, and sufficient understanding of what you want them to do and what sort of answer you should get so as to be able to tell if you’ve got it right and it is working, but once it is done correctly it can be re-used any number of times by just changing the contents of the input cell and reading the answer out of the output cell.

Today I used my spreadsheet formula building skill for something unrelated to my PhD project. For the past year I have been working on and off on making a hat by nålbinding. This has been my project which stays in my backpack in case I happen to be stuck in line somewhere, or attending a lecture, or otherwise have some time with nothing I need to be doing with my hands. A few people have asked me how long it has taken me, and while I know the date I started the project (18 May, 2008), I didn’t have a clue how much time has been spent working on it. So I set up a spreadsheet to figure it out.

I first looked up the formula to calculate the circumference of a circle (2μr) to be certain I had remembered it correctly from primary school (I had). I then told Excel to put the value for μ in cell A1 (one does this by typing “=PI()” into that cell). I then typed the headings “diameter”, “radius” and “circumference” into cells B1, C1, and D1. In the next row I set up my formulas. I measured the diameter of the hat (42 cm at the widest point thus far) and entered it into the empty cell in column B. In the adjacent cell I typed “=B2/2”, which told the computer to calculate the radius by dividing the diameter by two. In the third cell I typed “=2*\$A\$1*C4”, which told it to calculate the circumference by multiplying two times the value in A1 (which, as you recall, is Pi), times the radius. For the outside row of the hat this is 129.6 cm. Checking that with my tape measure confirms that this is pretty much correct (keeping in mind that the hat, being made of yarn, is able to stretch a bit, so obtaining an accurate measurement can be tricky).

(It is important to remember that in Excel, if you copy/paste a formula and the formula contains a cell name, the pasted version will instead name of the cell which is located in the same position relative to the new cell as the originally named cell is in relation to the original formula location. This is useful when you want to perform the same calculation over and over again using different starting data. However, sometimes you want the formula to point to a specific cell no matter what. In that case you add the \$ symbol to the name of the cell. This is why the above formula contains both cell names with and without the \$ symbol—I needed them in the name of the cell containing the value for Pi, because I wanted to use that particular value every time I calculated the circumference, but I didn’t want them in the name of cell containing the radius, because I knew that I would soon need to be calculating the circumference for lots of different radiuses.)

Having set up the first step, it was time to do the real calculations; if I want to know how long it took to make the hat, I need to know how many knots it took to make it. This hat is constructed by tying knots in a spiral pattern, starting from the center, and working outwards in what could be thought of as one continuous row, or could be thought of as a bunch of rows, each one out from the next. The latter interpretation is more useful for my purposes today, so first I counted the number of rows from the center to the outside (75). Then I measured the diameter of the innermost loop of that spiral (0.8 cm) and typed it into the 75th row of the spreadsheet in column B. I then entered “=B2-B75” into random cell out of the way of where I was working, and then (in cell F75) divided that result by 74 to get the width of a typical row (if there are 75 rows then there are only 74 spaces between them, which is why I used 74 and not 75 in that formula—if you don’t believe me, look at your hand and count the number of fingers and compare that total with the number of spaces between them). I then typed “=B2-\$F\$75” into cell B3, and then copied that formula into all of the rows between that cell and cell B75. This gave me the diameter of each row. I then copied the formulas for radius and circumference into all of those in between rows as well, giving me 75 rows worth of steadily decreasing circumference totals. From there it was a simple matter to add them all up (not quite 6000 cm worth of total distance for an ant to crawl if it started in the center and followed the spiral out to the edge), and multiply by the number of knots per cm (about 5), giving me a grand total of almost 27,800 knots in the hat. I then set my stopwatch running and made a few knots, and determined that if all goes well and there are no tangles in the yarn, I can do a knot in about 10 seconds, more or less. Assuming that this rate has been typical throughout the project, the computer tells me that I’ve put in about 77 hours work on this hat, which averages to about 1.7 hours a week (or less than 15 minutes a day) since I started it. (Keep in mind that some weeks it would not have been touched at all!) All in all, this sounds like a reasonable amount of time, particularly since it mostly exists as a project to keep me from being bored when I’m waiting for something.

So, the next time you hear a student complaining about their math class that they will “never need this stuff”, you can assure them for me that, yes, actually, it does come in handy, sometimes in very unexpected ways.

#### 1 comment:

Mel said...

I also recently used my Excel spreadsheet skils on a non-thesis related problem. 5 groups went in on a 1/2 buffalo. Trying to divide all the different types of cuts, pre-packaged weights of each cut, and different prices for each cut among 5 people was made so much easy by setting up an Excel spreadsheet.
When the order finally arrived I entered in the actual weights of each cut received. It then only took a few minutes to double check the data entry and voila. Everyones' alotted cut of each meat, the price per pound of those cuts, and their total price for their order. Done and done.