eWorkBook Challenge: spreadsheets and other calculation workbooks

Over past few years noticed several challenges such as a photo a day, a sketch a day, and 500 words a day, some of these challenges are based on a year, others over shorter periods such as a month or week.  A quick search of the internet didn't reveal any similar challenges for spreadsheets or other calculation workbooks such as MathCAD, in the main the challenges are to create an electronic workbook [eWorkBook] for a specific purpose, or otherwise use spreadsheets to track progress with other challenges: such as  tracking progress  writing a 50,000 word novel in a month at an average rate of 1667 words/day. It would therefore seem there is no existing challenge for creating a collection of spreadsheets.

May be such challenge isn't considered appropriate, or it is just too complicated for an individual to pursue. Most people don't have a need for a large variety of spreadsheets, they just produce multiple spreadsheets based on one single template. Those people working in technical fields probably already have the eWorkbooks they need for their work, whilst others create new eWorkbooks everyday in the course of their daily work. However it is also clear that people are looking for technical spreadsheets to carry out various engineering calculations. So there are people who can use eWorkbooks but not build them, or otherwise don't want to put the effort in at the time they need them.

By now I would expect that most graduates from formal educational programmes would have a toolbox of eWorkBooks for the types of tasks they will commonly encounter. Though unfortunately a lot of those they create as a formal part of their courses are probably created in software the likes of MathCAD and/or MatLAB: the problem with such is that such software is expensive and not all that readily available in organisations. Spreadsheets however are a lot more readily available, and therefore the better choice for building a toolbox of calculation tools.

The challenge isn't intended to be a competition with winners and losers, there are no prizes. The challenge is a personal goal, that anyone can take up, the prize if any is a useful personal collection of calculation tools. If wish to share such tools then I recommend up loading them to ExcelCalcs. The challenge can be spread over any time frame, suggestions are:

1. One eWorkBook a Month (12 for a year)
2. One eWorkBook a Week (52 for a year)
3. One eWorkBook a Day (365 for a year)

Which provides the opportunity to spread the challenge over a period of 3 years, the first year starting with one spreadsheet a month, then slowly building up to one spreadsheet a day for the third year.

I have already uploaded 37 unique files to my ExcelCalcs profile, so that represents enough spreadsheets to cover one workbook a month. Given that some of these are zip files, with at least one containing more than 40 workbooks, that also means enough workbooks to cover one workbook a week. Searching my archive hard-drive I find over 70,000 workbooks of various formats (As Easy As, QPro DOS, QPro Windows, MS Excel), many of those are duplicates or minor variations on a theme. My main toolbox already holds over 700 MS Excel spreadsheets. So producing some 365 eWorkBooks doesn't seem an impossible challenge: though creating something unique from scratch each and every day could be challenging. However the challenge doesn't have to be about creating from scratch, for me the challenge would be cleaning up the existing spreadsheets and reformatting so that they are suitable for release for others to use.

eWorkBooks, spreadsheets or otherwise can be classified into the following types:

TYPE 1: Reference Library
These eWorkBooks are databases or tables of data which are largely constant, and used as lookup values for calculations. They include such things as materials libraries, properties of chemical elements and such things. {MS Access or other DBMS potentially the better tool for this}

TYPE 2: Data Collection
These are eWorkBooks that are used to collect variable data, and create data tables and simple lists. Examples would include book lists and price lists. {MS Access or other DBMS potentially the better tool for this.}

TYPE 3: Tabulated Calculations
Tables of independent parameters and calculated dependent variables, with or without charts or graphs illustrating such relationships. {MS Access or other DBMS potentially the better tool for this.}

TYPE 4: Text Book Formula Sheets
These are the type of eWorkBooks typically uploaded to ExcelCalcs and complying with their recommended template making use of XLC. Such worksheets typically restricted to a single A4 page, have a sketch to illustrate the parameters of the mathematical model, show the algebraic formula, show the numbers substituted in the formula and then show the numerical result. These are primarily for the purpose of studying and understanding the formula.

TYPE 5: Calculated Reports
These are full technical reports, at least as far as the calculations go. Simply combining a series of Type-4 spreadsheets would not be appropriate for such reports. Such approach would generate a large amount of paper if so printed, or waste of hard-disk space if printed to pdf files, and most importantly a waste of time to read. Such calculated reports should be kept compact and concise, details of calculations are hidden behind the scenes, the reports display input parameters, and calculated results, but not necessarily the process of transformation from input to result. The user of such reports is expected to make independent checks on the results using other tools.

TYPE 6: Calculators and Dashboards
These can be simple or complex, either way no printed report is generated. These obtain input parameters and calculate results, how such transformation takes place is not shown. The purpose of such tools is rapid decision making. Such tools are likely used by persons checking Type-5 workbooks.

TYPE 7: Applications
These eWorkBooks can make little to extensive use of visual basic for applications (vba) and custom dialogue forms. The main features are that the workbook has the simple interface of Type-6 calculators, but can produce the reports of Type-5, or otherwise more detailed trace similar to Type-4, as well as produce tabulated reports based on variations in magnitude of input parameters similar to Type-3. Also likely to include data collection (Type-2), and rely on information extracted from libraries (Type-1).

TYPE 8: Function Libraries
Libraries of additional functions., which extend or otherwise simplify the calculation capabilities of the primary workbook application (eg, MS Excel).

TYPE 9: Utility Library/Add-In
Extends the features of the primary application (eg.MS Excel), but not the calculation capability. (eg. XLC).

TYPE:10: Other
Those types pf spreadsheets not mentioned elsewhere.


A spreadsheet to plan and track spreadsheet creation can be downloaded here.

---

Revisions:

1. [22/06/2015] Original
2. [23/04/2016] Change download links to MiScion Pty Ltd Web Store.

Technical Library Structural Design

Taken from the ExcelCalcs.com website.

---

Related Posts:

Bundle of my ExcelCalcs UpLoads

The bundle can be downloaded from MiScion Pty Ltd : Spreadsheet Bundle

Summary of Posts on my Journal about Software

Most of my spreadsheets can be found at ExcelCalc's, with sample printouts of some of the spreadsheets found over on my scribd profile. Since my spreadsheets largely make use of visual basic for applications (vba) via the use of a central add-in technical library (schTechLIB), and functions also use data access objects (DAO), along with MS Access databases for section properties as well as a central Excel spreadsheet, the spreadsheets are not so easy to set up and use. Consequently I have written some posts over on my Metamorphs Journal {Metamorphs = Beyond Structures} to provide further background. I have also been making other spreadsheets available via my blog. ExcelCalc's is really for presentation of calculations via the use of XLC, where as my primary interest is putting the calculations to work behind the scenes.  Presentations of calculations on a printed page has relatively limited options, where as user interfaces and user experience requirements produces calculations in a multitude of different forms. {ExcelCalc's policies therefore largely prevent me from replicating what is already there: seemingly trivial stuff I don't need to download, as been there done that, and created myself many years ago, but also not able to upload my variations either. Well actually I probably can upload, but that may then spawn a debate about replication: I don't want to go there. ExcelCalc's is largely a repository of calculations and/or formula rather than tools to perform the calculations.}

Designing the user interface is also not my domain, its the behind the scenes number crunching that I focus on. Also it isn't one-off calculations or point-value calculations which interest me, but calculations across some domain. So not the point-value calculations to design a single structure which is of interest, but the limitations of the available structural materials for a given structural form. For example what is the maximum envelope which can be provided by the available cold-formed c-sections in the form of a rigid moment frame? What is the largest practical parallel chord roof truss that can be fabricated and installed? How do these limitations relate to manufacturing economics? These are interests and questions, actually answering them is another matter.

Any case here are some links to the posts written so far on spreadsheets and software:

Plane Frames

1. Plane Frame Analysis
2. Plane Frame Analysis Front End
3. Plane Frame Analysis Alternative Front End
4. Plane Frame Analysis Front End Version 4
5. Plane Frame Analysis Back End

Other Structural

1. Barriers Design
2. Steel Design AS4600
3. Materials Library
4. Simple Shed Calculator for fixed base Gable Frame, using Kleinlogel Formula
5. Soil Heave Calculator
6. Contents of schTechLIB

Miscellaneous

1. Exporting Blogger Posts to individual html files
2. Managing files, and Google Chrome Bookmarks

Technology and Calculations

Created a new blog to provide additional background information and usage instructions for my spreadsheets over on ExelCalcs. Just about all my spreadsheets make use of schTechLIB (.xls or .xla) at least the ones uploaded to ExcelCalc's. This is a function library, it contains Excel user defined functions (udf) for various Australian materials and loading standards. These functions deal with all the conditional tests in the codes and select the appropriate expression to evaluate.

I use vba because it is more readable and easier to maintain than complex worksheet formula, additionally many of the functions started their life written in either Turbo C or Turbo Pascal. I like solutions which are usable across multiple applications. A worksheet formula can only be really used in Excel, whilst a vba function can also be used in MS Access or Word, or in various CAD packages, and functions are not so difficult to translate to other programming languages such as Delphi, and typically can be used with out change in VB.net.  As I have written in many other posts calculations are a means to an end. For me presentation of calculations in an Excel worksheet or else where is a pretty useless end. Whilst I was studying and when I graduated, I wanted to present the calculations, and something like XLC would have been great tool to have. However, calculations are typically an imposition placed on people by regulations and an obstruction and hindrance to people getting things done. An obstruction which was not there in the past. Whilst I consider it is necessary to demonstrate something is fit-for-function and suitable-for-purpose before it is made, I still consider that the imposition and delay caused by getting others to produce calculations is an obstruction to be removed. Even building and testing prototypes is dangerous if no rational assessment has been carried out based on scientific knowledge: which at the end of the day is little more than past experience and observations of the world and nature with possibly some mathematics thrown in to provide to measures to quantify what would otherwise simply be qualitative understanding.

So I create spreadsheets and write software as a means to break down that barrier, that obstruction to getting things done. However thus far all my effort has been into cutting the time frame, I still do the structural design work, and I still represent a barrier to be removed. Not removed altogether, but removed from the individual project, removed from the individual question that someone may have.

Answers to engineering questions delay drafters getting on with producing drawings and documenting a project, and delay fabricators and builders getting on with building. For something which is truly original and novel this is not an issue: however the vast majority of engineering calculations are not applied to anything which is truly unique and original. Unique and original requires determining the calculation or mathematical model, it does not involve multiple iterations of known mathematical models: it does not involve writing expressions down, stuffing numbers into and evaluating the numerical result. Unique and original requires developing a new mathematical model based on available fundamental theory or otherwise doing the science to develop a theoretical basis for design. So my basic view is: if doing real engineering then starting with a blank slate and no national standard and no formula in any handbook is of any use.

If we get away from engineering (as I perceive it to be), then we are concerned with established technologies and the need to adapt and modify these technologies to better suit our needs. The more typical task therefore is adopting, adapting and applying established technologies. We ADOPT an existing technology which is near suitable to our needs, we ADAPT it to better suit our needs, and then we APPLY that technology to meet our needs. All established technologies provide a heritage and a foundation up on which we build current and future technologies.

In case not immediately apparent, society itself is a technology. Technology is merely a set of tools and techniques we employ to get something done. To foster cooperation, to achieve economy of effort, humans developed their societies and their cities. But globally we have shortages in the up take, implementation and application of the established technologies. Shortages of water, food, housing, schools and hospitals to name a few. Much of this is caused by poor planning, design and management. But another major obstacle is empire building by occupational cliques. When knowledge isn't shared and it becomes a commodity then lives are placed at risk: buildings, bridges, machines they all start to fail when least expected. The failures are not unpredictable, the failures are typically avoidable if the right decisions had been made by the right people, and those people had access to the appropriate knowledge.

If something is in a national standard then that knowledge is not intellectual property (IP), the actual form and presentation of that knowledge may be copyright and IP, but not the knowledge itself. If someone is hindered in what they want to do by a national standard then they need to be enabled and empowered to remove that hindrance and comply with the standard. Compliance is there for that individuals own good as well as the rest of the community. Profits of publishers should not get in the way of achieving compliance with codes of practice. Job protection of certain professions shouldn't get in the way of achieving compliance. The regulations and resources required to implement were put there to protect the people: not provide an eternal source of income for monopolistic businesses. The primary requirement is to protect the people, such that the otherwise harmless actions of an individual when taken collectively across many people does not cause harm to the population at large and the individual. The resources required to achieve such protection can change at any time.

I therefore do not consider it necessary to get an engineer to "do the numbers". The numbers come from mathematical models which are an abstraction of reality, and unless the mathematical model has been been validated by scientific testing: then the model can be pure fiction. When there is no mathematical model then need an engineer to create the model and manage the testing and validation of such model. For most things however, the mathematical models exist already. Well actually no they don't, that's why we get failures.

For the mathematical models to exist already, we would need to have people in place who manage knowledge and information about technology, rather than just about mathematics. So for example we have beam theory: that is a mathematical model which has had some scientific validation and roughly reflects the behaviour of beams for practical purposes. However a floor beam is not the same as a rafter, nor: a purlin or girt or wall stud or lintel, or drive shaft or crane jib. Each and everyone of these beams has characteristics which are important to the design of the beam concerned which extends beyond beam theory. These other characteristics in the main have zero to do with the formal education of engineers: and society thus far relies on industry to pass on such information. Industry has proven unreliable as guardian of such knowledge. Thus the technology which is industrial society has a major flaw in it.

{interruption}

There was the printing press and then there were computers. The printing press allowed the dissemination of knowledge across large distances and across time. Prior to the printing press, books were rare and held in great and remote libraries, and books had to be written out by hand. Further few people could read and write, thus knowledge was typically passed on by word of mouth from generation to generation and thus highly reliant on human memory. Whether communicating via the spoken word or the written word, communication of the intended message is highly dependent on the interpretation of the transmitted message by the receiver. A book can get the message to people but its not necessarily understood or found useful. A lecture can provide the message to people, but once again its not necessarily understood. The application of knowledge is thus hindered by the limitations of communication and the opportunities to learn and the availability of a good teacher who can properly assist learning. Formal education systems tend to take learning out of the individuals hands and as a result actually hinder learning by imposing schedules which are not compatible with the individuals acquisition of understanding. {Analogously it becomes increasingly difficult to climb a ladder if someone keeps knocking the rungs out.}

Just as machine tools improve the speed and consistency of manufacturing processes, computers increase speed and consistency in the application of mathematical models and calculation. Technology is concerned with developing tools which aid human limitations, which extend and enhance human ability. Education systems seem built around highlighting an individuals inadequacies and then getting in the way of the individual adapting to the imposed necessity. That which the schools call cheating, in in fact the toolmaker humans ability to adapt and improvise. Inventing technology is a human trait: it is not the exclusive domain of some subspecies called engineer.

{interruption}

---

Interruption probably a good thing, this was meant to stay practical not get idealist. But still that's what I do, so may as well post.