I made a confession to my wife last night. "Honey, I don't think I have ever deleted a spreadsheet." I know, that sounds like an odd confession to be making, so let me explain. I was planning out our anniversary trip that we are taking this year and I had to keep everything sorted and organized. When you are trying to find the cheapest option of hotels, flights, and rental cars between four different destinations it can be overwhelming. I'm glad I only gave us one option for an airport to fly from. To keep this ordered, I made a spreadsheet. Then I added a little schedule to it so that we would have our week planned out in advance. This is probably due to the fact that growing up, whenever we went on vacation my parents would have all sorts of activities planned out for us. (Imagine their horror when they took us to Disneyland and the biggest thrill for us kids was swimming in the hotel pool at 10 PM or going up to visit my cousin in Seattle that we saw maybe once every 3 years and we just wanted to play Nintendo with him.) My wife has experienced this whirlwind vacation method, but since it is our anniversary and there will be no kids around I have trying not to have us worn out, if you know what I mean.
Back to the story, my wife asked why I was doing this and I explained to her the reasons above (including the not being worn out part - ouch, that got me slug in the shoulder). She then said, "So after your done are you just going to delete it?" You see my wife is a business major and she believes that spreadsheets are for long drawn out projects that may carry over from year to year (like budgeting). Once your done, you can hold on to it for a year and then delete it. I being an engineer realize that spreadsheets can help you make many of the not life altering but more important than mundane decisions. Not only that, but the decision may come up again, and you don't want to have to re-create it.
Case in point. I made several spreadsheets for our home remodel project. One was used to develop the budget (notice I said, develop). I went to Lowe's and got prices on everything, wood, drywall, insulation, screws, flooring, wire, paint, etc. Then I used this to go through and figure out how much an interior wall, an exterior wall, a floor, and a roof would cost. Then with my plans I only needed to know the length of the interior walls, the exterior walls, and the square footage of the floor (since the roof has to cover the floor you can double this). To this I would add costs for doors, windows, fireplaces, etc. and we had our basic cost of the remodel. Then as we did the remodel, I kept careful track of all of our expenses so that I knew how much I actually used plus I can see what tools I had to buy/rent. This is very useful if we decide to move and renovate again. It is also useful as we look at buying a flood house (I'll write about this later).
I checked my personal folders at home and I have 66 spreadsheets (many of these have multiple interconnected sheets some of which I have since turned into databases). I would guess that the number of spreadsheets I have at work number more than 100. So what kinds of things do I make spreadsheets for.
NCAA College Football - for about 4 years, I had my own rating system for the Division I-A football. I stopped it when I started a job working rotating shift work and a lot of my weekends were occupied. Plus, it involved a lot of data entry. The good news about it was, by the final week of the season, my formula came up with a top 25 that closely matched the final AP and Coaches polls.
Tax Software - one year I put the 1040 Form in a spreadsheet, with all of the subforms I needed. Call it a poor man's tax software. This was before the times of free tax software and before I was willing to fork over money for tax software.
Rental Property Analyzer - This is a nice one which I have used several times in deciding what rental property to buy. Basically, it takes into account all of the expenses associated with buying and managing properties, and compares it to other investment options that I would be considering.
And the list goes on and on. Which brings me to my favorite spreadsheet story. I had started a new job working in design engineering and going to be there 4 months. After about two weeks of training and getting me familiarized with the company, they gave me my assignment. When they gave it to me, they told me it might be a little tedious and boring, but they figured it would take most of my 4 month time frame and being the low man on the totem pole, I was it.
What they had were the raw data files (.txt) of 10,000 plus trials for an experiment. Each trial had a half dozen different variables that they needed to analyze. Further more, each variable had time points for 10 - 24 hours in one second increments. They wanted graphs made of them so they could have a visual representation.
First thing I do is open a spreadsheet, import the data and make a graph. For about 1 day, I worked with my boss to get the way they wanted the spreadsheet to look down. Then the fun began. I wrote a script to do all of the rest of the work. It sounds easy, and it was, relatively. I spent about a week and half writing the script and perfecting it. The biggest issue I dealt with was figuring out that I should only save after processing every 50 files and it would go so much quicker (I started out with every 1 and it was too slow). Then I set the script to run right before lunch and went out to eat. When I came back I was all done, and I went and told me boss. He thought I was done for the day. I corrected him and told him I was done with the project and did he want the graphs printed in color or black and white.
He was shocked to say the least and had to come and see the spreadsheets. I showed him and walked him through the process that I used. When he found out that the processing time for the whole shebang was only about 45 minutes he decided to talk to the engineer that needed this data. Lo and behold, we had plenty of time to make improvements to the graph and add all sorts of bells and whistles: trendlines, regressions, data overlays, you name it. The best thing was, since the script was doing all of the work, all I needed to do was add a few lines of code to it and 45 minutes later, it was done. In the end, the 10,000+ graphs ended up being the best looking graphs the department had ever produced. Word got around and I became the defacto go to guy when large amounts of data needed to be analyzed.
No comments:
Post a Comment