Architectural Units in Excel

Here we give out excel formulas to do math with architectural measurements (e.g. 14′-3″).

I recently had to calculate the total area of our soft goods inventory.  With over 80 drapes, that’s a lot of number crunching.  Luckily our inventory is in an excel file, which includes the height and width of each drape.  Okay, make a formula to calculate the square area from those values… Problem: the measurements are given in feet and inches (e.g. 12′-1″).  Excel treats this as a string, hence it cannot use it to do math.  Solution: the following formula will strip out the excess characters and convert the measurement to inches — success!

Formula #1

Convert a 13′-10″ style format into inches.  (Replace instances of C3 with the cell containing the measurement to convert.)
=(LEFT(C3,(SEARCH("'",C3,1)-1))*12)+(SUBSTITUTE(SUBSTITUTE((RIGHT(C3,((SEARCH("-",C3,1)) -1))),"-",""),"""","")))

Formula #2

Calculate area from two cells (C3 & D3) from a [feet]’-[inches]” style format.  Returns area as square feet.

=(((LEFT(C3,(SEARCH("'",C3,1)-1))*12)+(SUBSTITUTE(SUBSTITUTE((RIGHT(C3,((SEARCH("-",C3,1)) -1))),"-",""),"""","")))*((LEFT(D3,(SEARCH("'",D3,1)-1))*12)+(SUBSTITUTE(SUBSTITUTE((RIGHT(D3,((SEARCH("-",D3,1)) -1))),"-",""),"""",""))))/144

The only requirement is that the format must be as follows: 5′-3″
These formulas rely on this format to find the numbers. Perhaps when I find more time, I’ll update them to work with a few different styles.

Flattening Cones

Update:  I just updated this frustum calculator with javascript versions that will run from the browser.  One will calculate the flattened frustum from the radii, the other uses the circumferences of the frustum shape.

Have you ever wanted to lay out the frustum of a cone on a flat surface?”
“A what?”
“A frustum, the part of a cone that is left when you lop off the top.”
Oh, yeah. If I had a nickel… sadly, I’m still poor.

It can be a little intimidating if you’re not familiar with the concept of how to go about it. Our draper came to me wondering how she could accurately lay out a dress based on an actor’s measurements. (Apparently the traditional layout method wastes fabric.) So I came up with this simple excel sheet that does the calculations based on the following measurements: waist (small circumference), hem (large circumference) & inseam (height of the frustum.) It gives back the details needed to lay the cone frustum out on a flat surface. The diagram below gives the general gist of what’s what. There is a larger version of the image in the excel document.

From fabric to sheet goods, I hope this calculator finds some use.

Update: There is a great description of how to flatten a frustum in AutoCAD over at Woodweb.

Roll Bender Forces Chart

How much force does it take to bend 3”x16ga box tube to a 5’ radius in one pass? What’s the largest steel shape you can jam in a typical scene shop-built manual roll bender without cracking a roller? A week ago I didn’t know the answer to either one of these questions, now I’ve got a pretty good idea.

We’re talking about making a roll bender at work, and are waffling over whether to build a manual or motorized bender. I realized that we didn’t really have a handle on the forces involved in bending various steels to various radii; these numbers are instrumental when making this decision. (Not to mention during the design process.) Earlier this week I sat down and created an excel file to shed some light on the situation. Figured I might as well share it with my peoples.

Snag the files here: XLS or PDF

The majority of the worksheet calculates for:

  1. The force required to bend a variety of steel shapes to various radii in one pass (à la motorized bender).
  2. Adjustments for three different roller/die distances: 12”, 16” & 20” apart.

The manual pass sections are intended to show how much force would be needed when making the first pass in a manual roll bender. It calculates:

  1. The force required to reach the yield point of each steel shape.
  2. The resultant deflection and radius achieved in the first pass.
  3. The above calculations adjusted for three roller/die distances (12”, 16” & 20”).

Big effin disclaimer!
Lets be honest folks, I’m not an engineer. Confidentially (just between you and me) I am confident that the calculations are fairly accurate. But under no circumstances will I claim that they are 100% accurate. The reason is this: the last thing I need is a lawsuit because someone too lazy to verify the math took it in the eye from a flying bit of steel. Don’t you dare cut corners.

This worksheet is only intended to give a general idea of the numbers involved when bending steel. It cannot be counted on to give precise data concerning specing and designing of a specific machine. There are no allowances for the inherent springy-ness of mild steel, dodgy steel quality and varying manufacturing tolerances.

Bending steel is indeed a fudgy art. But now you’ve got some numbers.