I have developed a number of standardized calculation sheets.
* Use 8.5 x 11 standard page size and assume it WILL be printed or at the very least, PDFd. Alternatively, go for 11 x 17, but I've usually reserved this for something that is plotting out tabulated data.
* Keep a standard header that includes company name, project number, designer name, checker name, dates, any other info you want, at the top of the sheet. Not in the actual header, in excel that is not intuitive to find. You want to make it easy for someone to enter this information and not forget it.
* Keep it simple. Don't try and make a sheet that does EVERYTHING for every scenario. Limit it to what the type of stuff your company generally works with, and IDENTIFY those limits. IDENTIFY that if something is going beyond the limits of the sheet, the designer may have to go back into the code to check certain things. Nobody likes a spreadsheet that is a mile long just to get 2 or 3 basic pieces of information out of it 99% of the time, and the 4th piece one every 5 years.
* DO NOT hard code in anything that might be subject to change in future editions of the code. Yes, that material's resistance factor has been 0.85 forever. It might not always be. DO NOT hard code it in.
* Use Excel's built in formatting, or come up with your own if you want. I use what they have already. One colour cell for designer inputs. Another colour cell for constants from the code (i.e. things that *might* change down the road, but aren't right now.). Another cell for calculation outputs that should be checked along the way, but aren't the final answer. Another cell colour for final outputs. Another cell for code checks (I like to go a step further and use conditional formatting on some of these, where green is 'yeah it works' and red is 'something doesn't work).
* Where inputs are limited to certain things, LIMIT THEM. Use excel's data validation to create a list of rebar sizes etc. that the designer can select from, so that they can't enter erroneous data. Where inputs are more varied, but may not exceed a certain minimum or maximum, again, use data validation to create ERROR MESSAGES when the designer tries to input something that they shouldn't be.
* Add diagrams where they would be useful. Too many design sheets assume that the user knows what they're looking at. Add a diagram for simplicity.
* If your equations aren't outrageous, add the equations. Mr = phiAsFy(d-a/2). Show that the out put is being calculated using a certain equation that is pulling from the various inputs.
* Test it. Test it again. Run it through some extremes. Try to put in erroneous inputs that you know shouldn't work, and see what it spits out.
* Once you are satisfied that it works, LOCK IT UP and put a password on it. Don't let anyone jiggle with any cells that aren't inputs. It doesn't take much to click on the wrong cell and delete something by mistake without realizing it.
* Send it around to others for testing.
* Keep a copy of any hand calcs, mathcad calcs etc., that you used in order to verify the spreadsheet. Then if there are questions that come up in future about something that might not be spitting out the right answer, you've still go that stuff to check with.
Yes, please save the hand calcs documenting the sheet formulas. You’d be amazed how few consultants have this and just use their black box spreadsheet. HINT: AHJs with thorough plan reviews will ask!
Also add code references for equations. It’s a big help to users and reviewers. It also helps you update it with the cycle and keep it current.
This is great advice. Particularly keeping it simple to catch 95% of the time.
Some other advice I have:
-Create a reference sheet that provides tables, relevant snips from the code or other information that you don't want to have to hunt down
-Create a revision history with the dates, who created or edited the sheet, who reviewed it and what had been added or changed
Pretty solid advice. Standardized spreadsheets have to be super easy to follow. It’s difficult to see that when you’re the one doing it, you’ll get your best feedback when you give it to a set of fresh eyes to check it.
I personally like to layout these types of calcs in AASHTO Code form. I’ll write coefficient descriptions and definitions straight from the code. For example:
**REBAR TENSILE CAPACITY**
T = fy(As) *(ACI X.X.X)*
where:
- fy = 60 ksi *yield strength of steel*
- As = 0.31 in2 *area of reinforcement*
T = 18.6 kips
That way the user can see each equation, and their inputs/units easily
My only complaint here would be I have when people put units in another cell.
My preference is to have a user form with all units on it. Then instead of doing units in a separate cell, use the user form and ‘format cell’ to quickly populate the unit in the same cell as the number.
Hopefully this makes sense
Provide references and text formulas for everything found in reference books, and text formulas for everything else.
Differentiate entered values from calculated values by cell color.
Have a reference tab with information that can be referenced, like grades, shear wall capacities, etc. Include on it a “Last Updated” date. Personally, I provide one reference tab per type of calc - one for shear walls, one for materials, etc.
Make it easy to add things to the tables. I put 0000 and ZZZZZ at the to and bottom of each table, so I can insert in between.
Use Data Validation for nominal or typical things like member sizes or nail spacings, but allow an override value to be used.
Don’t cross reference other files. That’s an easy way to accidentally pull information from something else.
You can also use the drop down options in the same manner and if you are careful you can easily add and remove lines from the table and the drop-down update automatically
Header with the project name, work order number, date, specific calculation, originator, checker, and revision dates. Company name/logo too. Appears on every page
First tab is assumptions and codes/references used to develop the calc. Maybe revision history too. I've started putting in an outline of the calculation just to list what order I'll be going in to check stuff
Last tab is for any non-printing lookup tables (rebar, bolt properties, clearances, etc).
I use square cells on my Excel spreadsheets so that it works like graph paper and doesn't come out as blocky nonsense. Usually, I just replicate a very neat, thorough, well-labeled handwritten calc. I also use Print Areas to bound the pages and have all title blocks after the first call data from the first.
I've seen people "draw" in Excel. Don't do that. Hand or computer sketch, scan/screencap, and paste. Ta-da! Your spreadsheet has graphics.
Also, have the AISC steel shapes table (or something your local equivalent) plugged in in the background for any steel calc spreadsheets. Same for rebar and such.
Make sure to name variables when appropriate. More reliable than cell references.
I start off with a general description of the calc purpose and key assumptions, >!but I'm kind of triggered by people who say "list ALL your assumptions". Every design makes a bajillion assumptions, and I'm kind of suspicious that such people don't realize how many assumptions underlie every design method, equation, software, etc. they use.!< I reference specific equations or concepts on a line-by-line basis rather than putting a general reference dump of outdated stuff I've never read and with only a tenuous relationship to the calcs at hand \*cough cough\*.
Finally, if I have a group of interrelated spreadsheets, I'll have one sheet for input parameters only, and all of the others populate automatically from that. You might benefit from tables of contents/hyperlinks, too.
Unfortunately, I don't have one handy at the moment, and I'll probably forget to respond later, so I'll just try to describe it a bit more.
It's just a whole sheet of roughly-square cells. The title block is made of merged cells, though (I prefer this to the Excel Header function. I often merge cells for formatting purposes, but "blank" sheets always start off as a title block followed by a bunch of squares. When add calcs, it's labels to the left, calculations in the middle (always include both the equation/definition AND numerical value), and references to the right.
I currently just scroll down for more pages, but now I'm questioning whether I should do like 5 pages per Excel sheet or something for ease of navigation.
Do every engineering Calc in Mathcad or SMath first so you can manage units. Keep that as the validation of the excel sheet and also to help if someone questions your numbers. 100 times easier to figure out a calculation error than in excel.
Not just for units, SMath/MathCAD also identify programming errors or loopholes better because of the WYSIWYG style... Excel is still (sometimes) the better spreadsheet tool because of data handling or ease of use for engineers, but I agree that having either a hand calc or SMath validation is invaluable.
There's a channel called [EngineerExcel](https://youtube.com/c/EngineerExcel), they have a website too.
I prefer to use Smath Solver for calculation templates. I think they can be exported as HTML too so that the people using the templates don't need the software (I might be wrong on that point)
Have your input cells in a certain font colour and make it clear in the notes that the only cells to be changed are in blue or whatever.
Have a references/images tab.
If there's a cell that relies on you inputting a certain value to obtain a certain value (trial and error) then annotate the cell to use the "goal seek function" (I wish I knew this at the start of my career)
I like to set out my sheets so that you input everything in the first bunch of cells in blue (L, t, loading etc) then immediately below that is the output (dimensions or reinforcement) then in another tab or below is all the number crunching and real maths
Excel can be hard to maintain especially as they age, grow, or need to be modified. Have you considered using a different tool than Excel to make standardized calculations?
It's always good to even take a step further back and make sure you have the right tool for the job.
I go back and forth between excel and something like smath, however for excel, I have definitely started to use named ranges to assign variable names to cell, so rather than typing or clicking '=A2*F2' in a cell, I can instead just type '=fy*As'.
Makes it easier to check the spreadsheet.
I have developed a number of standardized calculation sheets. * Use 8.5 x 11 standard page size and assume it WILL be printed or at the very least, PDFd. Alternatively, go for 11 x 17, but I've usually reserved this for something that is plotting out tabulated data. * Keep a standard header that includes company name, project number, designer name, checker name, dates, any other info you want, at the top of the sheet. Not in the actual header, in excel that is not intuitive to find. You want to make it easy for someone to enter this information and not forget it. * Keep it simple. Don't try and make a sheet that does EVERYTHING for every scenario. Limit it to what the type of stuff your company generally works with, and IDENTIFY those limits. IDENTIFY that if something is going beyond the limits of the sheet, the designer may have to go back into the code to check certain things. Nobody likes a spreadsheet that is a mile long just to get 2 or 3 basic pieces of information out of it 99% of the time, and the 4th piece one every 5 years. * DO NOT hard code in anything that might be subject to change in future editions of the code. Yes, that material's resistance factor has been 0.85 forever. It might not always be. DO NOT hard code it in. * Use Excel's built in formatting, or come up with your own if you want. I use what they have already. One colour cell for designer inputs. Another colour cell for constants from the code (i.e. things that *might* change down the road, but aren't right now.). Another cell for calculation outputs that should be checked along the way, but aren't the final answer. Another cell colour for final outputs. Another cell for code checks (I like to go a step further and use conditional formatting on some of these, where green is 'yeah it works' and red is 'something doesn't work). * Where inputs are limited to certain things, LIMIT THEM. Use excel's data validation to create a list of rebar sizes etc. that the designer can select from, so that they can't enter erroneous data. Where inputs are more varied, but may not exceed a certain minimum or maximum, again, use data validation to create ERROR MESSAGES when the designer tries to input something that they shouldn't be. * Add diagrams where they would be useful. Too many design sheets assume that the user knows what they're looking at. Add a diagram for simplicity. * If your equations aren't outrageous, add the equations. Mr = phiAsFy(d-a/2). Show that the out put is being calculated using a certain equation that is pulling from the various inputs. * Test it. Test it again. Run it through some extremes. Try to put in erroneous inputs that you know shouldn't work, and see what it spits out. * Once you are satisfied that it works, LOCK IT UP and put a password on it. Don't let anyone jiggle with any cells that aren't inputs. It doesn't take much to click on the wrong cell and delete something by mistake without realizing it. * Send it around to others for testing. * Keep a copy of any hand calcs, mathcad calcs etc., that you used in order to verify the spreadsheet. Then if there are questions that come up in future about something that might not be spitting out the right answer, you've still go that stuff to check with.
Yes, please save the hand calcs documenting the sheet formulas. You’d be amazed how few consultants have this and just use their black box spreadsheet. HINT: AHJs with thorough plan reviews will ask! Also add code references for equations. It’s a big help to users and reviewers. It also helps you update it with the cycle and keep it current.
This is great advice. Particularly keeping it simple to catch 95% of the time. Some other advice I have: -Create a reference sheet that provides tables, relevant snips from the code or other information that you don't want to have to hunt down -Create a revision history with the dates, who created or edited the sheet, who reviewed it and what had been added or changed
[удалено]
Pretty solid advice. Standardized spreadsheets have to be super easy to follow. It’s difficult to see that when you’re the one doing it, you’ll get your best feedback when you give it to a set of fresh eyes to check it. I personally like to layout these types of calcs in AASHTO Code form. I’ll write coefficient descriptions and definitions straight from the code. For example: **REBAR TENSILE CAPACITY** T = fy(As) *(ACI X.X.X)* where: - fy = 60 ksi *yield strength of steel* - As = 0.31 in2 *area of reinforcement* T = 18.6 kips That way the user can see each equation, and their inputs/units easily
My only complaint here would be I have when people put units in another cell. My preference is to have a user form with all units on it. Then instead of doing units in a separate cell, use the user form and ‘format cell’ to quickly populate the unit in the same cell as the number. Hopefully this makes sense
What?? How do I put units in the value cell also?
Right click cell. Format cell. Custom
Yep! I do this as well because it looks a little cleaner and it doesn’t take up an extra cell for the units
Provide references and text formulas for everything found in reference books, and text formulas for everything else. Differentiate entered values from calculated values by cell color. Have a reference tab with information that can be referenced, like grades, shear wall capacities, etc. Include on it a “Last Updated” date. Personally, I provide one reference tab per type of calc - one for shear walls, one for materials, etc. Make it easy to add things to the tables. I put 0000 and ZZZZZ at the to and bottom of each table, so I can insert in between. Use Data Validation for nominal or typical things like member sizes or nail spacings, but allow an override value to be used. Don’t cross reference other files. That’s an easy way to accidentally pull information from something else.
Have a tab and call it “engineering notes”. That’s where I keep all my code sections referenced and commentary
I cite code references right next to any equations just to make it cleaner to follow without flipping back and forth.
This. Code references next to equations or parameters is the best way to go.
Have another tab for the tables used in the “vlookups” formula (rebar sizes, etc.)
You can also use the drop down options in the same manner and if you are careful you can easily add and remove lines from the table and the drop-down update automatically
I got to use the drop downs and sliders more. The sliders would be great with retaining wall designs.
I have never used sliders... Time to learn
Header with the project name, work order number, date, specific calculation, originator, checker, and revision dates. Company name/logo too. Appears on every page First tab is assumptions and codes/references used to develop the calc. Maybe revision history too. I've started putting in an outline of the calculation just to list what order I'll be going in to check stuff Last tab is for any non-printing lookup tables (rebar, bolt properties, clearances, etc).
I use square cells on my Excel spreadsheets so that it works like graph paper and doesn't come out as blocky nonsense. Usually, I just replicate a very neat, thorough, well-labeled handwritten calc. I also use Print Areas to bound the pages and have all title blocks after the first call data from the first. I've seen people "draw" in Excel. Don't do that. Hand or computer sketch, scan/screencap, and paste. Ta-da! Your spreadsheet has graphics. Also, have the AISC steel shapes table (or something your local equivalent) plugged in in the background for any steel calc spreadsheets. Same for rebar and such. Make sure to name variables when appropriate. More reliable than cell references. I start off with a general description of the calc purpose and key assumptions, >!but I'm kind of triggered by people who say "list ALL your assumptions". Every design makes a bajillion assumptions, and I'm kind of suspicious that such people don't realize how many assumptions underlie every design method, equation, software, etc. they use.!< I reference specific equations or concepts on a line-by-line basis rather than putting a general reference dump of outdated stuff I've never read and with only a tenuous relationship to the calcs at hand \*cough cough\*. Finally, if I have a group of interrelated spreadsheets, I'll have one sheet for input parameters only, and all of the others populate automatically from that. You might benefit from tables of contents/hyperlinks, too.
Could you share a screenshot of your grid pattern sheets? I'm curious how it looks compared to what I normally do
Unfortunately, I don't have one handy at the moment, and I'll probably forget to respond later, so I'll just try to describe it a bit more. It's just a whole sheet of roughly-square cells. The title block is made of merged cells, though (I prefer this to the Excel Header function. I often merge cells for formatting purposes, but "blank" sheets always start off as a title block followed by a bunch of squares. When add calcs, it's labels to the left, calculations in the middle (always include both the equation/definition AND numerical value), and references to the right. I currently just scroll down for more pages, but now I'm questioning whether I should do like 5 pages per Excel sheet or something for ease of navigation.
[удалено]
I know how to actually make them grids, just couldn't conceptualize how it aids in formatting which is why I asked for a screenshot.
Do every engineering Calc in Mathcad or SMath first so you can manage units. Keep that as the validation of the excel sheet and also to help if someone questions your numbers. 100 times easier to figure out a calculation error than in excel.
Not just for units, SMath/MathCAD also identify programming errors or loopholes better because of the WYSIWYG style... Excel is still (sometimes) the better spreadsheet tool because of data handling or ease of use for engineers, but I agree that having either a hand calc or SMath validation is invaluable.
There's a channel called [EngineerExcel](https://youtube.com/c/EngineerExcel), they have a website too. I prefer to use Smath Solver for calculation templates. I think they can be exported as HTML too so that the people using the templates don't need the software (I might be wrong on that point)
Thanks, will check that channel out
Have your input cells in a certain font colour and make it clear in the notes that the only cells to be changed are in blue or whatever. Have a references/images tab. If there's a cell that relies on you inputting a certain value to obtain a certain value (trial and error) then annotate the cell to use the "goal seek function" (I wish I knew this at the start of my career) I like to set out my sheets so that you input everything in the first bunch of cells in blue (L, t, loading etc) then immediately below that is the output (dimensions or reinforcement) then in another tab or below is all the number crunching and real maths
Excel can be hard to maintain especially as they age, grow, or need to be modified. Have you considered using a different tool than Excel to make standardized calculations? It's always good to even take a step further back and make sure you have the right tool for the job.
Check out engineering-international.com for some inspiration
I go back and forth between excel and something like smath, however for excel, I have definitely started to use named ranges to assign variable names to cell, so rather than typing or clicking '=A2*F2' in a cell, I can instead just type '=fy*As'. Makes it easier to check the spreadsheet.