Category: excel


Some assumptions:

525,600 minutes per year
75 years per lifetime
39,420,000 minutes per lifetime
13,140,000 minutes of sleep
26,280,000 waking minutes


196 school days in the year
6 hours in a school day
360 minutes in a school day
70,393 minutes in a school year
13 years of primary education
915,107 minutes of primary education
3.48% of your lifetime’s waking minutes getting a primary education!

4 years of secondary education
281,571 minutes of secondary education
1.07% of your lifetime’s waking minutes going to college!


261 working days out of the year (no vacation)
8 hours in a working day
480 minutes in a working day
125,143 minutes in a working year
45 year career
5,631,429 minutes of work in a lifetime
21.43% of your lifetime’s waking minutes going to work!


17 weeks of NFL season
3 NFL games per week (average watched on teeve)
3 hours per NFL game
9,180 minutes of NFL watching per season
30 seasons of NFL watching
275,400 minutes of NFL watching per lifetime
1.05% of your lifetime’s waking minutes watching NFL! (almost the same number of minutes you spend in class in college).

Aren’t spreadsheets great? I could do this all day . . .

The joy of Solidworks configurations

Last week I met with some local machinists to work out the best way to produce some new AR-15 handguards we’re workingon, and now I’m ready to go. I whipped out the final design:


The magic of Solidworks, the program I use to design parts, is a feature called configurations (I think I went over this a few years ago concerning some other parts I made). You can design the basic part, and then use configurations to more or less automatically produce similar parts with different dimensions. Well, it’s not automatic, but it’s a lot simpler than designing a new part for every, say (as in my case), barrel length you need to support.

The parts above are for barrel lengths of (left to right) 10½, 14½, 16 and 20 inches. I use the configurations feature with a chart like this one to come up with the different parts:


The green-tinted cells are fixed constants, basically the numbers I use to derive all the rest. I started with a design for a 16 inch barrel and worked out how many recoil grooves I need (the slots at the top of the part) as well as the length of the top of the rail. Because the recoil grooves are 0.394 inches center on center, all the rail lengths are increased or reduced from the 16 inch barrel length by multiples of 0.394 inches. That’s where the spreadsheet comes in handy.

Thought I’d be able to come in and get more work done seeing as it’s a national holiday, but the phone started ringing off the hook an hour ago and it’s driving me nuts (I’m not answering it, fuck it, it’s a holiday). Time to take a walk.

The anal life

I use Excel for organizing stuff with computers. It doesn’t scale, but I haven’t made the time to learn SQL. All our part numbers and bills of materials are in a massive spreadsheet I started in 2003. We outgrew the spreadsheet years ago, but I don’t have an alternative now. We tried a software-based inventory management system; it was a disaster.

Once I clean up all the shit on my desk, it gets filed:


All the file tabs are printed in 10 point Arial Bold. I have files at home going back to the 1980s. Nothing gets thrown away.

I can’t stand sorting through boxes of fasteners looking for screws or whatever. Each separate fastener gets a labeled drawer in the parts organizers:


They don’t make those steel walled parts organizers anymore, but I can find them on eBay.

Shop furniture

I need to build some shop furniture, mostly roll-round utility tables, but I will also need stands for things like the drill press, grinders, etc. Rather than re-invent the wheel every time, I decided to settle on a single design I can use for a variety of applications.

Back in 2005 when I moved my office from our first 700 square foot building on Whittier Ave to a 1,500 square foot unit on Monrovia, I spent Memorial Day Weekend building five workbenches out of structural Douglas fir, some pine boards and 5/8 inch sheathing:


It’s a very basic design. I didn’t see it anywhere, it just seemed a natural way to build a bench. Those were assembled with deck screws; later I would use 1/4 inch lag screws in most places. I covered the benches with sacrificial 1/8 inch Masonite (hardboard) that can be removed and replaced after it gets worn, a trick I learned from my father:


When that photo was taken, there was only one person working in the warehouse: me!

Those benches are still in use, and I made a few more five years later when we moved across the parking lot to Unit B1. When we moved across town to Baker St I asked my production manager if she wanted to get fancy proper workbenches and she said she liked the ones we had, so we made some more. I think we have ten of them now.

In the meantime I also constructed some big 8×4 foot worktables of pretty much the same design (with no shelves) for use in the shipping and receiving area. When we moved to Baker St I made some more, but fitted them with casters so you could move them around the shop easily (note the assembly benches in the background):


This proved to be a remarkably useful innovation. We have four of those big roller benches and we use them constantly. It’s incredibly productive to be able to stack stuff up on a bench in your working area and then simply roll it out to the shipping area, or vice-versa. Which reminds me of an anecdote from Nassim Nicholas Taleb concerning wheels on airport luggage . . .

So I decided that from now on utility benches would have casters.

For my woodshop I am looking at a different design, this one of 3/4 inch birch plywood. It’s something I have seen around on YouTube. Also, the woodshop next door to us when we were on Baker had a bunch of these (they later added casters to them after they saw our roll-around benches). All my projects are assigned random project names (from a spreadsheet, natch), and this project is called Jade:


I’ve been trying to work out how large to make my Jade bench(es). The big 8×4 foot bench is too big for my space and anyway the eight foot span probably tests the strength limits for this particular bench design. Luckily, my design software, SolidWorks, lets you easily develop what it calls configurations, that is, variations on a single basic design that differ by dimensions or other changes. So after designing my Jade bench, I whipped up a number of different configurations by changing the main table top dimensions, which are all at a 2:1 aspect ratio (that is, the width is half the length):


SolidWorks would allow the widths of the stretchers and legs to be changed for each configuration to make them more proportional, but I wanted to keep things simple, so in all cases the legs and stretchers are five inches wide.

Of course, there’s a spreadsheet at upper left in the illustration. That’s to work out the bill of materials (or more specifically the cut schedule) for each bench. The green tinted cells are variables, that is, dimensions that might be specific to a particular bench size. Once those are entered the rest is calculated automagically.

Non-USAn readers might note with dismay and alarm all the fractions that appear in the Length and Width columns of the spreadsheet. These are truly the American Man’s Burden, and what set us apart from the rest of the world far more profoundly than rampant gun nuttery. In fact, I have often wondered whether any other culture in the world besides the US and the British use fractions at all, for any reason (and the British are migrating away from them now too). The worst part is when you have to do math with fractions. Luckily, we have spreadsheets for that.

Naturally, going metric for this sort of thing would be practical, but here is why I won’t:

For my mechanical design work I use decimal inches, no fractions. This is easy for me and easy for the machinists, who just punch decimals into a CNC program. From that point of view, calling out 2.763 inches is no easier or more difficult than using 7.018 cm.

Mathematically, fractions can be more accurate than decimals. For example, 5/32 is much more precise than, say, 0.156, which is rounded up from 0.15625. But today computers and calculators just work with the floating point, so fractions don’t give you anything when doing calculations. And when measuring you are subject to tolerances anyway. If I want to use a dimension of 5/32, it will be called out as 0.16 if I can live with a tolerance of 0.015; and 0.156 if I need it to be accurate to within 0.005. I never get to 0.15625, practically speaking.

The problems occur once a tape measure gets involved. All the tape measures I have ever seen are in fractions of an inch. So are the yardsticks and just about every other device for taking linear measurements except a few engineering scales I have laying around (my digital micrometers can switch at will, of course). So anything architectural, or in the woodshop, uses fractions of an inch.

If I really cared (and maybe if I was raising kids I would care more), I could round up every tape measure and ruler in the house and in the shop and trade them all in for metric equivalents. Within a week of using a metric tape measure I suspect centimeters and millimeters would be as natural to me as inches and quarter inches. After all, it didn’t take long for me to find driving in the UK as natural as driving in the US.

But there is no compelling reason for me to make the switch, especially as I can do the math with Excel, when necessary.

How to buy a truck

Having nothing but big powerful V8s around the house is all fun and games until you live up next to the mountains and need to drive up and down a lot of hills. Then it gets hella expensive. So I am shopping for a V6 pick-up truck. I do this using Craigslist and Excel.

It will be a month or two before I can scrape the dollars together to buy the truck, so in the meantime I am collecting data from Craigslist. I check Craigslist every day and put all the relevant properties into a spreadsheet:


Note the listings are sorted by “Score.” What is this “Score?” you ask. Well, I needed to be able to use objective criteria to rank different trucks from best to worst. I did this by calculating feature factors and then multiplying them to arrive at a final score. The higher the score the better the deal. The feature factors are in hidden columns, and unhiding columns M through Q reveals the calculations:

  • Year factor simply takes the model year and divides by 2016.
  • Mileage factor averages all the odometer readings of all the listings, and divides that number by the current listing’s odometer reading. If the current listing does not have an odometer reading, the error is captured and the number 1.00 is inserted as a mileage factor.
  • Cab factor. If the listing has a king cab, the cab factor is 1.75.
  • Shell factor. If the listing has a shell, the shell factor is 1.20. Obviously a king cab is worth more to me than a shell (in fact, I probably won’t buy a truck without a king cab).
  • Price factor is calculated similarly to the mileage factor: the prices are averaged, and that number is divided by the current listing’s price.

So for the final score all you have to do is multiply the factors together.

The listings with the highest scores have outrageously low asking prices (generally there is something wrong with them), while the lowest scores have very high prices or very high mileage. But the scores in the middle are for cars I am most likely to want to inspect and maybe buy. As I add more cars to the list, the scoring system will make it easier to sift through the listings.

While working on this last sheet I discovered you can mix data types in Excel! For the cab and shell factors, I needed to be able to put the actual multipliers (1.75 and 1.20 respectively) in cells somewhere so the feature factors could be derived from them. I do it this way instead of building the numbers into the formulas so I can quickly change the multipliers for all the cars (generally, as a best practice with spreadsheets, I figure out all the constants I’m going to need and then put all those in separate cells, often on a separate sheet, so that I can easily change the constants as desired).

But I didn’t want to have to add a new column just for each multiplier, and I didn’t want to set up a new constants worksheet.

So on a hunch I used Excel’s text functions to derive the multiplier from the column header, “Cab Factor (1.75),” as shown:


See what I did there? I did a calculation on the number contained in the last four characters of the string, save one. And fuck me if it didn’t actually work! So all I have to do to change the multiplier is edit the column heading, making sure the number is four characters including the decimal point and there’s a close parenthesis following it.

It’s actually pretty amazing you can mix data types like that, and definitely no accident.

A friend advised me to avoid Ford Rangers. I used to be dogmatically opposed to Fords, based on experiences my family had in the 1970s. But as I get older I have put ideology aside. My RV is a Ford, for example. Two of my best friends drove nothing but Ford Rangers for years, one after the other (this was during my anti-Ford days and I always made a face when I got into one of their cars).

Now I realize if Ford or anyone else that wasn’t supported by the government (like FIAT) made consistently bad cars, they wouldn’t be in business. And to that point Ford has been, over the last decade or two, the most successful car manufacturer in the world. So I guess they don’t really make crap after all.

As for Toyota Tacomas, all the Tacomas I have seen on Craigslist either have outrageous mileage (like 250,000 miles) or outrageous price tags. Perhaps that’s an indication of their essential quality, but I am not buying a car with over 200,000 miles, nor am I paying twice the right price for one. But thanks to my spreadsheet, if a reasonably priced Tacoma with moderate mileage comes on the market, I’ll know to jump on it.

Powered by WordPress & Theme by Anders Norén