Reusable Excel Spreadsheets Complex Design Problems
I'm not so good at memorizing long and complex formulas. I can derive them from basic principles, but I don't have enough room on my hard drive to have a whole bunch of formulas I can get to easily.
For example: you know πR2 is the area of a circle (let me check my math book...just kidding). You can change it to: π(D/2)2. You can refine it further to: (π/4)D2. Well I decided to forgo punching in: π x D ^2 / 4. I just punch in: 0.785*D^2. That's an easy formula to remember and it saves my precious fingers from punching away needlessly. Now you could be that clever one that says: "hey! You punch in 7 numbers for the first one and 8 for the second one. You have to punch in 1 more number + the number of digits in D!" Well...you're right. Let me have my moment of feeling somewhat clever.
This brings me to the point of doing the brunt of your work up front and letting it pay dividends. Sure, I can use my TI 30x IIS at home or my TI Voyage 200 at work. But I use them for hand calculations that I store in my notebooks. I also use them to double check my after I make a spread sheet. This is a valuable lesson: if you can't do the calculation by hand then don't put it into a spread sheet. Because if you can't do it by hand then 4-5 months from now you still won't be able to do them, and you won't understand the spreadsheet you made.
In the first video I create a lookup table. This is useful if you're changing materials and you can't remember what page you found one constant or another. You can also use them to see what material is better for a particular property. I use these a lot when I do heat transfer or thermodynamic calculations--especially steam tables (I'll get into this in a future article I'm planning about heat transfer and thermo).
In the second video I show you how to use Goal Seek in excel. In my second paragraph I mentioned πR2 and I decided to use it as an inspiration. I show you how to use goal seek when you have upper and lower limits on air line pressure. This comes up when you're using an E/P, an I/P, or certain valves that can't exceed certain pressures. For this example I use simple pneumatic cylinder sizing.
Sr. Mechanical Engineer, Solidworks EPDM administrator
8 年Sabotaging spreadsheets is low! I recently made a spread sheet for heat transfer w/ nodal analysis. I didn't put convection in and was thinking about why the numbers didn't make sense all weekend. I thought "how can the surface temperature be constant without convection?" I told excel the surface temperature should be 100 deg C, and was expecting the temperature to drop the further down the pipe I went, but the surface temperature was 100 deg C all the way to end! I put in convection for the fluid and convection for the surrounding air and the problem was solved. Got a nice temperature gradient, which is what I was expecting. That's where spreadsheets fall off, if you assume the numbers are correct and design around it then you'll be in for a surprise. Got to pay attention to what's going on and you have to understand the principles involved.
Social media dieting: Reducing checking/posting to once/week with personally justified exceptions. Please forgive. Also attempting to remain reciprocal: If one comments or emoji my posts, I might do the same on theirs.
8 年This is true. I met people so use to using a spreadsheet for wind load that it is highly unlikely most (people that use them and the managers that oversee the data) could do the calculations long hand without quite a bit of effort. Only those who use to do the calculations frequently before the spreadsheet was created or those who created the spreadsheet are that able to figure out what is going on with the spreadsheet data. Also the spreadsheet data is so pretty looking it hardly seems as suspect to people looking at the data unless for some reason what they want to install is not happening because of a number on it. The Excel spreadsheet system for quickening a process is nice, but it will sort of retard people into not completely understanding a process. It is sort of like looking at a digital clock and not having a concept of how hours and minutes pass on their various cycles. The further problem is that an Excel spreadsheet has to be secure. If not, people can get into those formulas and change them if they want to hurt coworkers in very bad ways.