State of Play: 2017/wk45

Drawing in Spreadsheets (MS Excel)

Method 1 : Cell Borders and Shading

Been looking at increased use of the drawing capability in MS Excel. First I discovered it is possible to set the cell sizes in millimetres or other units of measurement. Though as I suspected from using VBA, such settings are not accurate and do not “round-trip”. The width of a cell is defined by characters, any conversion between units of measurement is based on the average or maximum size character in the default font. The dimension gets converted to characters, and then the characters get converted to dimensions all with rounding: so going from one to the other and back again never reproduces the original dimension. Still can produce an approximate 5 mm x 5mm grid, and then use the cell borders and cell shading for some simple sketching of rectangular objects: such as floor plans and site layouts. The spreadsheet cells can be used to calculate scaled dimensions and otherwise add dimensions together. The disadvantage compared to CAD (computer aided design) is have to produce different drawings with different levels of detail.

Method 2: Shapes

Another approach to sketching in MS Excel is using the shapes layers. Whilst the grid can be used for snapping to and quickly setting dimensions of shapes, the discrepancy between cell width and cell height means the grid isn’t actually square. This can cause problems if setting dimensions of shapes, say representing furniture, to be placed on a floor plan set out using cell borders. That is to say shapes with properly scaled dimensions won’t fit the floor plan. For example if I set the grid to 5 mm x 5 mm, using layout mode, then return to normal mode, then return to layout mode:

  1. Normal mode: (Row Height: 14; Column Width: 2.17)
  2. Layout Mode:  (Row Height: 4.76 mm; Column Width: 4.76 mm)

Seems it does remain square (or so it says). I doubt there is any benefit staying in page layout mode. Anycase it is useful for quick sketching, and illustrative indicative drawings, rather than situations where require an accurate scale drawing.

Method 3: XY-Charts

The third approach for drawing in MS Excel is to use XY-Charts. This is a more time consuming approach as it requires working out all the point coordinates, and then joining the points with lines. Once again there is a problem with X and Y axes dimensions: it being difficult to get a square grid. There are macros around the web which contend to solve the problem: they don’t work. The benefit of using XY-charts is that the sketch can be parametric, using worksheet cell calculations. Parts can be added or excluded by making use of the function NA() to remove coordinates. Dimensions can also be modified. Simply scaling up and down of little value as default scaling of the chart always fits the object to the chart, so the drawing doesn’t change. If lock the scales of the chart axes then the object drawing either becomes too small to see or to large to fit the chart. This can be useful, as a drawing can be completed with high detail, then copied, and the axes scales adjusted to produce different views: for example the overall view of a truss and detailed view of the connections.

Whilst producing the XY-Charts is time consuming, the calculated coordinates can also be wrapped into script code for AutoCAD, the cells containing the script can then be copy pasted to AutoCAD LT or other Acad compatible CAD package (eg. ProgeCAD). The MS Excel XY-Charts thus provide a rapid prototyping capability for developing automated CAD drawings.

Illustrating Calculations: The Past

Most of my spreadsheets don’t have illustrative drawings. For my first spreadsheets I printed the calculations and then added illustrative sketches with pencil. Then I tried various cell based charting. The simplest being gantt charts using various techniques: most of which require a monospaced type font, so they were easier with MS DOS based spreadsheets (Lotus 123, As-easy-as. Quattro Pro). Gantt charts can be produced using the first drawing method mentioned above: but such method doesn’t automatically update when durations and start dates are changed. Other diagrams I have used involved schematics of irrigation pipe networks, with zeros and ones used as pipe valves. The diagram could be viewed to select  the correct valve and turn it on or off, which then linked back to the flow and head loss calculations.

However for structures I haven’t placed many illustrations in my spreadsheet calculations. My first attempts involved linking AutoCAD Lt drawings. The problem with that approach was that the drawings didn’t plot correctly from QPro or from Excel. A more serious problem however is that the spreadsheets became unstable and crashed often. Where the spreadsheet didn’t crash the workbooks were far too slow and cumbersome to be practical to use. So I opted for a two stage printing process. Plotting Acad drawings over the top off calculations. But slow process requiring direct access to a printer.

Next approach was to export Acad drawings to wmf files. To sketch in powerpoint, and to sketch using Visio Technical. But once again, too many images made the workbooks slow and unstable. Networking the printers also meant loss of the two stage process. So adopted the old printing press approach: drawings on separate pages (plates). SInce typically produce structural drawings on A4 or A3, they are typically bound with the calculations and so illustrate the intent of the calculations. The problem with that approach is that the specification drawings don’t fully explain the calculations, also if A3 drawings are used then the drawings are typically too extensive to bind with the calculations. So additional drawing sheets are produced to illustrate the calculations and bound in the report near to the associated calculations. Producing cad drawings is time consuming, so typically only use that approach for repetitive calculations. New calculations have freehand sketches added afterwards.

New calculations also typically produced completely freehand. Though typical approach is to set up the calculations in a spreadsheet if done more than 3 times. Since design is iterative the 3 times, is usually reached the first time a  calculation sequence is used for a project. For example not found suitable solution on third iteration, set calculations up in a spreadsheet for the fourth iteration, and use spreadsheet for any additional iterations. So if my issued calculations are freehand it means I got the desired solution on the first iteration. If my issued calculations are printed, it doesn’t mean I had the spreadsheet already, chances are I spent some two weeks or so developing the spreadsheet to complete the required calculations. (My fees won’t reflect this as will have an expectation, can recover the costs, by saving time on future projects: typically a bad idea)

Anycase limited in the number of inserted images, and by time, most of my calculations have no illustration. This can actually waste time attempting to figure out what the calculations are doing, especially for infrequent calculations, or those highly dependent on dimensions (eg. bolted end plate moment connection).

My simplest illustrations involve cell borders and diagonals to illustrate building shape: flat roof, skillion roof and gable. Others involve use of special characters to illustrate beams with supports and loads. Using arrow characters is useful, as the direction of the arrows can be changed based on calculations. My main use of this technique has been using three arrows to indicate whether an internal pressure was positive and pushing outwards or negative and pulling inwards.

Illustrating Calculations: The Future

Purlin Design

I have several different purlin design spreadsheets, ranging from a single page, to several pages. Each one is an attempt to better explain the detailed steps taken to account for local pressure factors. The latest versions have made increased use of arrow characters to illustrate the direction of loading on single span beams. This includes illustrating stepped loads and partial loads. The stepped partial load, either being at one end, or around the mid span. Still no matter how many times I create such spreadsheets and no matter how much detail I provide, it never seems clear. Though I’m not sure what I mean by “clear” or what I am really trying to achieve by the process. Basically it’s complicated, there are many factors to consider, so the solution will never just jump out off the page as obvious. It is something which has to be worked through. So the single page presentation gets the solution, with detail hidden behind the scenes in vba. The other multiple page presentations provide insight as to why the solution is what it is.

The main illustration required is one detailed the actual relationship between the purlin spacing, the normal loading, and the magnified local pressure extent loading. Easier would be to add some extra formulae and informative feedback indicating whether the local pressure extent is larger or smaller than the purlin spacing. If the local pressure extent is smaller than the spacing and span of the purlins it typically has negligible effect on the loading of the purlin. If the local pressure extent covers the purlin, then it has significant effect on the load and required size of the purlin.

So not so much the calculations which are an issue but feedback in the presentation to explain the critical factor having and impact on the design. Live loading is often a critical factor for closely spaced purlins. As the spacing of purlins is reduced to minimise the effects of wind load, the live loading starts to increase and becomes critical. So need to keep the area supported by a purlin large enough so that the magnification of live loading for small areas does not come into play.

So many factors to consider, and difficult to keep track of in my head, and many factors which are typically taken for granted, and only occasionally become an issue. When such rarely used factors do become an issue it may take some time to recollected and realise that they are the cause of the problem experienced.

So not sure whether adding more diagrams to the worksheet will help, or what form such diagrams need to take. I believe the main need is informative feedback, in the form of written notes.

Wind Loading

A while back now, I started a spreadsheet with XY-charts to illustrate the pressure distribution for the direction theta=90 longitudinal wind loads. This direction typically involves stepped loads with roof (-0.9,-0.5,-0.3,-0.2), and walls (-0.65,-0.5,-0.3,-0.2). {NB:  written from memory so may contain errors}.

The XY-chart shows a simple rectangular plan with stepped loads along the walls. Another chart intended to show the stepped load across a gable roof: but not completed that yet. I do have vba routines which split  the stepped load across the roof. These routines are used to get equivalent uniformly distributed load (UDL ),to use with kleinlogel formulae in my shed design spreadsheets. However to calculate the required coordinates for the XY-chart to plot the load distribution, I need to go back and do some freehand sketching and work out the necessary dimensions and coordinates, and suitable cell formulae. Some things are just easier and more efficient in VBA. Any case it’s a spreadsheet I will get back to at some point.

The main change for wind loading spreadsheets, has been increased use of cell borders to represent shape of building (monoslope or doubly pitched) and then identify the various factors building up the load on the roof. I have one version with two pages, one page with theta=0 and another for theta=90. I have still another with all directions side by side on the one page. These diagrams make use of arrow characters to illustrate the direction of loads.

Still the more recent approach is to use drawing methods 1 & 2 above to draw plans and elevations using shapes and/or cells. These diagrams then provide the dimension and geometry of the building under consideration, and then can have the pressure coefficients noted adjacent to the appropriate surface. In the process of setting up these diagrams, I decided to revisit my VBA wind loading functions, and decided to write some wrapper functions, so that I only need use a single VBA function to get the wind load on the surface of a building or canopy. I can then program some consistency into the application to real buildings. (NB: The wind loading code AS1170.2 only gives the pressure coefficients for a limited number of building shapes. So it doesn’t give pressure coefficients for the complex roof shapes of houses involving multiple hips and valleys. nor does it give pressure coefficients for an American barn style shed. Such pressure coefficients need to be determined by judgement preferably with some knowledge of fluid mechanics. For example I would contend the judgment of the person who put hipped roof into AS1170.2 to be flawed, their use of the term cross-wind is also spurious, and the added description to the tables has also messed their use up. That’s the second code in recent years, where recent revisions seem to have messed up a code (AS1657 is the other).)

Structural Form

Now have a several parametric spreadsheets for different structural forms. These cover building shape, trusses, and connections.

  1. Timber Framed House
  2. Footing Plan
  3. Gusset Plate Knee Connection
  4. End Plate Knee Connection
  5. Gable Frame Shed: frame outlines (cladding, inner and outer faces of frame, centre line)
  6. American Barn Style: simple outline shape
  7. Bolt groups: general
  8. Bolt Group: Circular Flange plate
  9. Collar-tied Roof Truss: outline
  10. Collar-tied Roof Truss (Timber): Detailed with connections
  11.  House and Attached Canopy (Collar-tied roof truss)
  12. Hipped Roof Outline (Plan and two elevations) {illustrate the effect of varying roof pitch to hips and main roof}
  13. Roof Truss (W-type webbing or inverted triangle)
  14. Deep roof truss (can have parallel top chords or sloping, various types of webbing)
  15. Profile of building with sloping walls
  16. Dome roof
  17. Storage with sloping wall (assists determine suitable slope for wall)
  18. Guard rail and barriers
  19. Timber connections and fastener spacings
  20. Roof and wall cross bracing (limited as cannot conveniently adjust the number of braces)
  21. Cold-formed steel rafters and column set out

Most of these also have Acad script, so that can check the calculated dimensions with scaled drawings in Acad LT. Poses a conflict as to whether should use VBA to generate scripts or use worksheet cell calculations. Also highlights another issue, the creation of such parametric sketches, starts with a freehand sketch and the derivation of formula to calculate the coordinates and lengths. For certain using the likes of ProEngineer or Revit is likely to be much easier and faster: though the users of Revit and similar building information models (BIM) don’t seem to have got much into parametric design. They still seem focused on putting lines onto paper. (NB: Users of 3D CAD also seem to have missed the point of drawing. Drawing is a means to an end, it is meant to be a simplified abstraction of reality focusing on that which is important. Producing a full blown 3D CAD model suitable for workshop detailing, before conceptual design has been completed is wasteful. Design starts with freehand sketches, if those sketches can be produced on computer and given constraints when needed then that improves the design process.)

Site Plans and Floor Plans

In considering ways in which people can produce sketches digitally, I started experimenting drawing floor plans and site plans. Since I have created a workbook with several worksheets for sketches at different scales. I have otherwise put this approach to use with my ideas concerning mobility, modular construction and tiny houses. The workbook contains sketches of recommended minimum dimensions for various rooms in houses.

Also been sketching various sizes of land for farming and houses. Along with sketches for separation distances of buildings and other objects of varying heights. Along with tie-down of shipping containers, and cars and trailers. The tie-down I produced using cell borders, whilst the car and trailer I produced using shapes.

The room layouts I produce using cell borders, but use shapes for dimension arrows. The cells can then be used to calculate areas, and otherwise calculate over all dimensions from known part dimensions.

One problem of using 5 mm x 5 mm square grid, and cell borders to produce sketches to illustrate calculations is the automatic widening of cells to fit numbers. Text typically over flows into adjacent cells, but numbers typically cause column widths to adjust: distorting the drawing. So it helps to merge cells before producing calculations. Typically need to merge at least two cells for decimal numbers, and 3 to 4 cells for numbers in scientific notation. Merging such cells can slow down the calculation process, having merged cells also hinders moving blocks of cells around.

An alternative approach is sticking with my 18 column worksheets, and otherwise setting up a sketching worksheet with 5 mm x 5 mm grid. Then use shapes to sketch on the sketching worksheet and then either copy or move to the calculation worksheet. Copying is preferable, so if have any changes to make, can delete from the calculations, modify the source and then place revised copy on the calculations: otherwise its cumbersome to match the sketch back to the grid or modify in place.

Another workbook uses an XY-Chart for a square layout village. It draws the travel path travelling around all the streets and calculates the total distance. The size of the village, width of roads and grid spacing can be adjusted. I created it when otherwise checking the 5km circular cells discussed in earlier posts.

Other Sketches

  1. Wetted perimeter of circular pipe.
  2. Spiral used for approximating space required by roller doors.
  3. Various GIS uses explained in earlier posts
  4. Section Profiles, to illustrate calculation of section properties (I-sections, Channels, C-sections, T-Section, web stiffeners, hollow flange beam)

Other Activities

Was attempting to extend my simple beam calculator, but got stalled by lack of published formulae for deflections, and moments along the span,  for some of the indeterminate beams. Since the formula tend to be complex, it is typically preferable to write VBA functions and use them rather than cell formulae. So the question arises, if using vba, is there any benefit in deriving the formula and publishing along with the workbook. Or instead write general purpose functions using approximate numerical methods. Both approaches have their benefits, so doing both would be preferable, however I have limited resources: so stalled for the moment.

Revisiting my shed and canopy design spreadsheets, attempting to merge together again has had in the beginning when using QPro. This was possible originally because the QPro spreadsheet generated data files for our plane frame program. Now that the plane frame is converted to VBA, other possibilities arise. But have issues concerning documentation of calculations versus the speed of getting desired results. All could be done rapidly in VBA, but that poses problems for documenting the calculations. What needs to be documented and what doesn’t is still an issue to be resolved. Not just a matter of illustrating calculations, but actually whether or not to present the calculations in the first place.

So in writing wrappers for the wind loading functions may also start to combine more calculations directly with the plane frame analysis program.

On other fronts been looking at Fiverr. This may be useful on two counts. The first is as a simple way to get an escrow like service, the second is getting assistance so I don’t have to do everything myself. Additionally fiverr also provides a market place. It allows prices to be set from $5 to $995 in US dollars. My price levels range from $110 to $5000 in Australian dollars. Ignoring the conversions it seems I can set up price  levels 1 ($110) to 6 ($880). Since I expect most projects to be small scale and below $550, it should work reasonably well. Even better If I can connect products from my website here, to gigs over on fiverr. Once set up, new customers will have to pay via fiverr, some unreliable older customers will also have to pay via fiverr. One issue however is the cost of the service on each transaction, so may need to raise prices. The fee seems to be around 20%, whilst debt collectors typically charge around 10%, on the other hand avoid the hassle of having to get debt collectors and potentially only start work for reliable payers in the first place.

The only real hassle I see with using fiverr is the need for the client to create an account with fiverr. This may not be suitable for business as accounts mostly oriented towards individuals. Whilst private individuals may have no need for an account on fiverr for a one-off transaction.


  1. [05/11/2017] : Original
  2. [20/11/2017] : Revised Levels possible on fiverr.