Start of Daily Challenge

The challenge is not some much as to produce one article each day, but to accumulate a total of 365 articles, so that have one article to work on each day there after. That is to say if have more than 365 articles and each requires at least a day’s effort to improve and maintain then, it will take more than one year to cycle through all the articles.

I previously considered a 12 month workbook challenge, a 52 week workbook challenge, and a 365 workbook challenge. Something I didn’t consider too much of a problem as I have a few thousand MS Excel workbooks, and most have multiple worksheets. The problem is finding workbooks suitable for release, or modifying workbooks to be suitable for release. Some of the workbooks need more than a day’s effort, and I started with the big ones, rather than the simple workbooks.

Shed Design WorkBook

The main workbook being my shed design workbook, which I no longer follow since my dad added a front end to it. All very colourful it is, with conditional formatting turning selections red if they fail the structural checks. The problem is it is crosslinked with my original parameter sheets, and with additional flags to check compliance with multiple checks. Whilst I organised data on individual worksheets, to match export requirements of tables to MS Access, my dad seems to dump data in any free space. Anycase the main issue is that when something turns red and fails I want to know why, so that I can make the appropriate parameter change. The second issue is I want to rip out my gusset plate connection calculations and replace with my detailed moment end plate connection calculations.

Part of the problem I have, is that, I merged all the report worksheets in my original workbook into a single worksheet, so that the report can be printed out in a single pass. At present point in time, I’m not sure how, but in Quattro Pro V1 Windows, and MS Excel 97, I used some feature which allowed defining a report across  multiple worksheets, and so could print whole report in one hit. However in moving from one project to another, not all the pages of the report are required, and extra pages may be added: which is one reason the definition of the report was never updated and our use of the feature was abandoned. Anycase I’m considering it may be better to rip the workbook down back into individual worksheets, and use macros to print the whole report.

Originally I had different workbooks for different Australian standards (eg. AS1170, AS1538/AS4600, AS1250/AS4100, AS1720), but having multiple linked workbooks when working over a network becomes cumbersome, and in terms of vba code doesn’t work like C or Pascal libraries. Multiple links also become a hassle when saving workbooks to projects. Originally I didn’t create workbooks for each project, just had a centralised tool box of workbooks, and used these workbooks to produce calculations for a project and printed results straight to paper: so no digital version of the reports. With linked workbooks, the project workbook can break, if make changes to the organisation of the central library, modify worksheets, or modify vba code. On the other hand I dislike vba code carted along with each workbook, I prefer to separate data from the means of processing that data.

Now part of the exercise in using spreadsheets to conduct calculations is to identify input parameters to a calculation sequence, and produce a table of such parameters. Once have a table of parameters, can then define input data collection forms and databases to store such parameters. Such parameter collections can then be used to generate additional outputs such as drawings, or directly control automated production systems such as CNC machine tools. The complication is that the validity of the input parameters is dependent on complex sequence of calculations. This is where my dad’s input form comes into play, flags, switches and conditional formatting check the validity of the input parameters: and an unskilled user can find a valid design-solution by a process of trial and error, but with out knowledge of the background calculations the solution is likely to be a poor solution.

Now in Quattro Pro for DOS, my dad had pop-up menus which jumped around the calculation worksheet, from one input to another, whilst in Quattro Pro Windows,  I had dialogue boxes to collect information. When moved to MS Excel 97, neither approach was as simple to setup. However the dialogue boxes had accumulated all the parameters onto a single sheet, and therefore just settled for input worksheets. However, connection details were worked through on the main worksheet, with local parameters, no collecting the parameters together in one place. Then we started creating multiple windows of the one workbook, and having one window looking at the input worksheet and the other looking at the report. With the multiple document interface (MDI) this was practical and convenient, it’s not so convenient now that the MDI has been abandoned by Office: creating inconsistency (It was not Excel that was inconsistent it was the rest of Office, compared to other Windows software. A dumb approach to user interface was that in Borland Delphi: not so bad if it’s the only application ever use, but if use multiple applications then it’s crazy. Either want to tile applications or the documents contained within an application.).

Since I didn’t have back up hardware, and my Windows XP computer failed, and OEM licenses were covertly introduced, I have no choice but to change to Office 2016/2017. With the changes made to the Excel user interface (UI), the use of multiple linked books is a greater inconvenience, than previously. Calculation wise it is preferable to be able to see the calculations and the input parameters together. So when move to a parameter in the input sheet, need the report to scroll to where that parameter is used. So the situation arises that when a project throws a spanner in the works, the new calculation sheets contain local parameters and are not connected back to the parameter table.

Another issue is presentation of the work sheets. Original calculations for connections were for welded and bolted end plates. For a shed there is the base connection, knee connection and ridge connection. Originally had three worksheets, and the calculations were printed out, over the top of AutoCAD sketches of the connections. The calculations involved were relatively trivial, and  I collapsed all  the connections to one page without diagrams: leaving the description to formal drawings of the connections. { Check example 103 for the original 3 page presentation}.

My next step was to increase the detail of the connection assessment, since there are two directions of loading, I divided the worksheets into two columns.  Typically downward gravity loading, placed in the left column of the sheet,  and upward wind loading (though sometimes wind can be downward), placed in the right column. To the checks I added combined actions to the bolts, and  checks on the welds. This extended the presentation back to 1 pages for each connection, but without diagrams. However the knee connection is a relatively compressed sequence of calculations and difficult to follow. If I haven’t designed a knee connection for a while, it can take more than an hour to figure out the worksheet and find a design-solution for a connection detail. Spreading the calculations out, and adding diagrams can extend the presentation up to 5 pages possibly more pages.

This is where the various presentation needs of calculations starts to become important. As I have indicated previously calculations are a means to an end, and not an end in themselves. producing pages and pages of calculations for regulatory approval does not benefit anyone: its a waste irrespective of whether printed on paper or saved in digital format (pdf files). Calculation is a process, and a true independent check does not involve the reviewer looking at the designer’s calculations, rather the reviewers task is to look at the designer’s specification and check if the specification is valid.

With pocket calculator I can bash numbers for simple calculations through the device without writing anything down, with a computer I can crunch numbers for more complex sequence of calculations. The only time I need to see the calculation sequence is when I do not get the answer I expect, or fail to get the results I seek.


  1. [16/04/2017]: Original [2017 Day 106 / Week 15]