Integrating Spreadsheet Templates And Data Analysis Into .
Journal of Industrial Technology Volume 16, Number 4 August 2000 to October 2000 www.nait.orgVolume 16, Number 4 - August 2000 to October 2000Integrating SpreadsheetTemplates and Data Analysis into FluidPower InstructionBy Dr. Bruce MarshKEYWORD SEARCHCurriculumFluid PowerTeaching MethodsReviewed ArticleThe Official Electronic Publication of the National Association of Industrial Technology www.nait.org 20001
Journal of Industrial Technology Volume 16, Number 4 August 2000 to October 2000 www.nait.orgIntegrating SpreadsheetTemplates and Data Analysisinto Fluid Power InstructionBy Dr. Bruce MarshDr. Bruce Marsh is an Assistant Professor with theDepartment of Industrial Technology at Texas A&MUniversity in Kingsville where he teaches coursesin Fluid Power, Energy and Power, Dimensional Metrology, Quality Assurance, Manufacturing Productivity and Data Analysis and Decision Making inIndustrial Technology. He is also the academicadvisor for the newly established Student Chapterof the Fluid Power Society.IntroductionFluid Power in one manner, shape,or form is found in most IndustrialTechnology curriculum. It may be taughtunder other names such as IndustrialHydraulics, Hydraulics and Pneumatics,or Industrial Hydraulics and Pneumatics.Regardless of its name, the main intent ofthis course is to provide students with afundamental understanding of fluidpower concepts with an emphasis on thephysical properties of fluids, componentdesign and operation, and circuit designand analysis.In fluid power instruction varioustechniques and training aids are used topromote understanding and learning.Many of the techniques and aids usedand the rationale for their incorporationinclude: (a) manual or computer-basedcalculations to promote an understanding of the principles of system andcomponent operation; (b) visual andschematic displays to promote anunderstanding of component designand construction; and (c) hydraulic andpneumatic trainers to promote handson experiences with working systems.Since the development of anunderstanding of system principles anddesign involves mathematical calculations, a spreadsheet program can beused to show time savings capabilitiesof custom-designed templates. Consequently, it is the intent of this paper todemonstrate various ways in whichspreadsheet templates and spreadsheetbased data analysis can be incorporatedinto fluid power instruction. It shouldbe noted that no attempt will be madeat this time to compare and contrastinstructional approaches or softwarepackages both generic as spreadsheetsor hydraulic-specific as AutomationStudio and HydraCalc; each approachand software has its own merits andadvantages provided the emphasis iseffective and meaningful integration.Spreadsheet Program Selectionand IntegrationAlthough there are a multitude ofspreadsheets programs available today,only two would probably be consideredfor fluid power integration, MicrosoftExcel or Lotus. Experience has shownthat Excel should be given integrationconsideration for three reasons: (a) it isan inherent part of the Microsoft Officesuite to which many universities,departments, and students hold licenseto; (b) its extensive charting andgraphing capabilities; and (c) its dataanalysis capability. If students arefamiliar with spreadsheet basics, only areview of the following would normallybe needed to get them up and runningwith fluid power-based templates. entering formulas and text; using and integrating functions; creating tables, graphs, and charts.Designing hydraulic and pneumaticsystems requires many simple calculations. Spreadsheet templates, onceconstructed, are a very appropriate toolfor performing these calculations. Withrespect to entering formulas, studentsshould be reminded that the “ “ signmust be entered prior to formula entry;without it, all formulas will be treated astext and will not be able to perform theirintended purpose. Functions, on the2other hand, are special purpose, formula-based expressions. MicrosoftExcel User’s Guide (1994) indicatedthat there are over 300 functions that aredivided into 12 categories for ease ofselection. The function selection dialogbox is accessible by selecting functionsunder the Insert menu. A useful aidincorporated with the function selectiondialog box is a sample expression of theway in which the function must beentered and a short description of itsintended use.Middleton (1997) indicated that thevisual display of data in charts andgraphs is an important aspect of anyanalysis. Chart and graph creation usingtable information is relatively easy inExcel through the use of the chartwizard. Various procedures for generating effective charts and graphs thatincorporate a pleasing appearance andintegration with established tablesinclude (a) using the “on this sheet”option instead of the “on new sheet”option when creating new charts andgraphs; (b) including column and rowlabels in charting cell range for automaticaxis labeling; (c) selecting the appropriate chart type (line, XY scatter, etc.) for agiven table data; (d) resizing of charts orgraphs for effective integration withexisting tables; and (e) determininginternal chart formatting, items such aslegend information inclusion or exclusion, number formatting, label orientationand sizing, and axis scaling.Fluid Power Problem-SolvingUsing Spreadsheet TemplatesOne of the most powerful facets ofa spreadsheet program is its “what if”analysis capability. Academic businessdepartments have known of andutilized this capability for many years.If a spreadsheet template is designedwith the intent of conducting “what if”
Journal of Industrial Technology Volume 16, Number 4 August 2000 to October 2000 www.nait.organalyses and incorporates proper cellreferencing and input data structuring,the usefulness of the template can besignificantly increased. One of theprincipal advantages of the “what if”analysis structure is the overall timesavings it provides; tedious andrepetitive manual calculations are notrequired. Most templates that aredesigned and constructed for integration in fluid power instruction willautomatically possess this capability tovarying degrees and can be modified tofurther enhance this capability.Two examples that demonstrate theuse and application of spreadsheettemplates in fluid power instruction canbe seen in Problem-Solving Templates1 and 2. Although both of thesetemplates have given values in theirproblem statements and questions, thestructure of the templates permit theentry of any value into the “givens”cells as well as the automatic recalculation of new solution valuesusing new “given,” provided cellformulas and references are enteredcorrectly. Another aspect of thesetemplates that should be noted is theirstep-by-step solution approach (singleformula calculations). This approachwas used solely for instructionalpurposes. In reality, most experiencedtemplate designers would combinemultiple solution steps into a singlestep formula expression.Regression Analysis of FluidPower DataSimple and multiple regression(macro-based) is an important aspect ofanalysis and is available in Excelthrough the Data Analysis option underthe Tools menu or through the Add-insoption under the Tools menu, if DataAnalysis is not listed as a Tools option.Simple regression analysis is alsoavailable using the Trendline option inExcel. This option, sometimes referredto as trend forecasting, is a graphicsbased approach for conducting simpleregression analyses. The usefulness ofthe trendline analysis lies in the abilityof users to know when and how toincorporate and utilize the differentregression analyses. Figures 1 and 2indicate, respectively, the trendlinetypes and display options availablewithin Excel.As mentioned previously, it isimportant for users to develop anunderstanding of the ways in whicheach of the trendline types can be used.For example, a comparison of Rsquared values of four trendline types(linear, power, logarithmic, andexponential) can be performed todetermine the regression method thatyields the maximum accountability ofvariability (the greater the accountabil-3ity, the greater the validity of theregression equation). To begin thetrendline analysis process, an XYscatterplot of the data is needed. Thescatterplot is not only a necessary firststep in the trendline analysis processbut it can also be used to determine if aregression analysis data is actuallywarranted. An added advantage of thetrendline analysis is that the regressionequation and R-squared value can beincorporated on the chart of the plotted
Journal of Industrial Technology Volume 16, Number 4 August 2000 to October 2000 www.nait.orgdata and will automatically update ifthere is a change in the data set.An example that demonstrates theeffectiveness of the trendline analysisand R-squared comparison techniquecan be seen in an attempt to simplifythe relationship between pressure,force, and area (PFA). As indicated inpublished textbooks, Industrial Hydraulics Manual (1992) and Esposito(1998), the PFA relationship is characterized through the following equation:Pressure (P) Force (F) / Area (A).This formula is used extensively todetermine system pressure requirements given a particular cylinder/pistondiameter and output force requirement;variations of the formula are also usedto conduct reverse order calculations.Regardless of the formula variationused, one aspect of all PFA calculationsis the need to convert cylinder/pistondiameter to an area. Since this conversion aspect adds an additional calculation to the entire process [ Area (A) 0.7854 * Diameter 2 ], a simplified,single-step formula expression wouldbe desirable. To determine the feasibility of establishing a simplified, singlestep expression incorporating pressure,force, and diameter (PFD) variables,the following four-step analysis wasconducted.Step 1A: Establishing Primary Variables and Data. To begin the analysisprocess, four columns of data weregenerated. Diameter data was selectedin increments of 0.25 inches startingwith one inch and ending at six inches.Area data was calculated from thediameter data and used, along with thetest force value of 500 lbs, to calculatetheoretical pressure values. Diameterand Theoretical Pressure data werelabeled as X and Y variables, respectively, for use in the Step 1B regressionanalysis.Step 1B: Conducting RegressionAnalysis of Diameter (X) and Pressure(Y) @ Force 500 lbs. The basis forthis analysis was to determine theextent of the relationship between theidentified X and Y variables as well asa valid regression approach andequation. It should be noted that anyregression equation derived from thisdata set would only be valid with aforce of 500 lbs. The results of thisanalysis, Figures 3A to 3D, indicatedthat the Power regression yielded themaximum accountability of variability(R-squared 1.0) and thus, the mostvalid regression equation. Thisequation, Equation (1), was: y1 636.62 x1-2, where y1 pressure (psi)and x1 diameter (in).Step 2A: Establishing SecondaryVariables and Data. It was noted that4the slope coefficient in the Powerregression equation changed wheneverthe Test Force was varied. Consequently, it was deemed that an equationcould be developed to replace theEquation (1) slope coefficient (636.62)to overcome the force limiting factor ofthe equation. Using the “what if”capability of Excel, the following tableof Test Force (X) and Slope Coefficient(Y) data was generated by varying theTest Force in the Data Table of PrimaryVariables and recording the slope
Journal of Industrial Technology Volume 16, Number 4 August 2000 to October 2000 www.nait.orgcoefficient change in the associatedPower regression equation (Figure 3B):Step 2B: Conducting RegressionAnalysis of Test Force (X) and SlopeCoefficient (Y). The basis for thisanalysis was to determine the extent ofthe relationship between the X and Yvariables in Table 2 as well as a validregression approach and equation. Theresults of this analysis indicated thatthree regression analyses, linear, power,and polynomial, yielded R-squaredvalues of 1.0 and any one of these threecould be used. The Power regressionequation detailed in Figure 4, wasselected due to its simplicity. Thisequation, y2 1.273 x2 where y2 forced-based coefficient and x2 force(lbs), was labeled Equation (2). Theregression equations of the linear andpolynomial regression analyses thatwere not selected for use were, respectively, y2 1.273x - 0.0004 and y2 9.0E-10x2 1.273x - 0.0006.Step 3: Pressure, Force, Diameter(PFD) Equation Development.As mentioned previously, the development of Equation (2) permitted thereplacement of the slope coefficient inEquation (1) and enabled the determination of pressure at any applied forceand diameter. Algebraically, theconversion was as follows:Equation (1)where,y1 636.62 x1-2y1 Pressure (psi)x1 Diameter (in)Equation (2)where,y2 1.273 x2y2 Forced-BasedCoefficientx2 Force (lbs.)Equation (3A)where,y1 y2 * x1-2Equation (1)coefficient (636.62)replaced with y2equationEquation (3B)y1 (1.273 x2) * x1-2Figure 1. Trendline Type. To access the trendline type dialog box, create an XY scatterchart, double click the chart to enter the edit mode, single click on a plotted point, andselect Trendline under the Insert pulldown. Adapted from Microsoft Excel.Figure 2. Trendline Options. The trendline options dialog box is accessible byselecting the Options tab whenever the trendline dialog box is displayed. If thetrendline has already been created but needs to be edited, double click the chart toenter edit mode and double click the regression line. Adapted from Microsoft Excel.Equation (3C-1) Pressure 1.273 *Force * Diameter –2Table 1. Data Table of Primary Variables5
Journal of Industrial Technology Volume 16, Number 4Equation (3C-2) Force Pressure /(1.273 * Diameter –2)Equation (3C-3) Diameter [ Pressure/ (1.273 * Force) ] –0.5Step 4: Pressure, Force, Diameter(PFD) Equation Validation. Equationvalidation is an important aspect,especially one that incorporates tworegression equations. To validate thePFD equation, the traditional, two-stepPFA equation was used to calculate thetheoretical pressure that would berequired to lift a force of 500 lbs usinga cylinder with a 2.5 inch diameterpiston. The results of this calculationwere as follows:Traditional PAF Equation:Pressure Force / Area 500 lbs / 4.91 in2 101.83 psiArea 0.7854 * (Diameter)2 0.7854 * (2.5 in)2 4.91 in2The tradition approach indicatedthat a system pressure of approximately102 psi would be required. Using thesingle-step Equation (3C-1), thetheoretical pressure was calculatedusing the same force and diametervalues, 500 lbs and 2.5 inch, respectively. The results of this calculationwere as follows:PFD Equation (3C-1):Pressure 1.273 * Force * Diameter –2 1.273 * 500 lbs * (2.5 in)-2 636.5 * 0.16 101.84 psiThe PFD equation indicated that atheoretical system pressure of approximately 102 psi would also be requiredto lift a force of 500 lbs using a cylinderwith a 2.5 inch diameter piston; formulavalidation was successful and can beused in fluid power calculations.SummaryThis paper is intended to aidprofessionals in the field into the ways6 August 2000 to October 2000 www.nait.org
Journal of Industrial Technology Volume 16, Number 4 August 2000 to October 2000 and means upon which a spreadsheettemplates can be integrated into a FluidPower instruction. The inherentflexibility and adaptability of spreadsheet templates in data and formuladriven classes make it a useful instructional tool. If spreadsheet integration issuccessful, student learning of FluidPower concepts can be enhanced;thereby, strengthening students’problem solving skills, facilitatingbetter and faster decision-making, andincreasing the their marketability asIndustrial Technologist. It should alsobe noted that the experiences providedthrough spreadsheet-based instructioncan provide students with valuableinsight for the utilization of thissoftware in other classes or on-the-jobbefore and after graduation.ReferencesEsposito , A. (1998). Fluid powerwith applications (3rd ed.). NewJersey: Prentice Hall.Microsoft excel user’s guide (version5.0). (1994). Microsoft CorporationMiddleton, M. (1997). Data analysisusing microsoft excel. Belmont,California: Wadsworth Publishing Co.Vickers Incorporated (1992) . Industrial hydraulics manual . RochesterHills, Michigan: Vickers Incorporated Training Center.Table 2. Data Table of Secondary Variables7www.nait.org
Two examples that demonstrate the use and application of spreadsheet templates in fluid power instruction can be seen in Problem-Solving Templates 1 and 2. Although both of these templates have given values in their problem statements and questions, the structure of the templates permit the entry of any value into the “givens”
Graph (Spreadsheet, digitizer, online graphing tools) Spreadsheet & Data Processing (Calc, excel, online spreadsheet tools - Zoho Office, Google spreadsheet) Checklist (Word Processing, survey tools, online polls, Spreadsheet) Chart (Spreadsheet, digitizer, mind mapping tools online
Presenter: Hello students, Welcome to this learning session on spreadsheet. Today we are going to learn about how to get started with Spreadsheet. Slide Title: Lesson Contents Presenter: In this video, you will learn about What a Spreadsheet is? What is a Spreadsheet Software? Examples of Spreadsheet Software.
Drafting a Spreadsheet Template Spreadsheet templates are Microsoft Excel workbooks consisting of multiple worksheets: three standard worksheets and one or more rule specific worksheets. The spreadsheet templates are used to collect 40 CFR Part and Subpart reporting requirements for
select Spreadsheet (Fig. 7.4 above) Why is a spreadsheet considered a database? Well, a spreadsheet is a data source document (it contains data that you want to use, so in that sense a Calc spreadsheet is a database and can be converted to a Base database. So you are importing the Address List spreadsheet into a Table in the database.
Converting Excel to XML The first thing you need is, of course, an Excel spreadsheet. Below, we have an example spreadsheet with six columns and 26 rows of data. The columns separate the last names, first names, ages, majors, GPAs, and school year of each student listed in the spreadsheet. Step 1 - Create your Excel spreadsheet. excel to xml
a Google Form is that it can automatically be entered into a spreadsheet. With the data in in a spreadsheet, you can use it as you would any other Google Sheets spreadsheet. To get started, you first have to tell Google the name of the Google spreadsheet in which you will store the responses. To view your responses in a spreadsheet, click the View
An electronic spreadsheet is superior to manual calculations because: i) The spreadsheet computes faster ii) The spreadsheet computes its results more accurately iii) The spreadsheet automatically recalculates whenever any data are changed iv) all of the above. 2. Analytical questions a. Who are the
analisis akuntansi persediaan barang dagang berdasarkan psak no 14 (studi kasus pada pt enseval putera megatrading tbk) kementerian riset teknologi dan pendidikan tinggi politeknik negeri manado – jurusan akuntansi program studi sarjana terapan akuntansi keuangan tahun 2015 oleh: novita sari ransun nim: 11042014
