CCAC SOUTH: SET-105 - Technical Computing Spring 2019



CCAC SOUTH: SET-105 - Technical Computing Spring 2019Assignment #5Excel AssignmentsRevised: 9/12/2019Student Name:Date:Assignment#5 contains multiple assignments. Your instructor will tell you which are required.You are to turn in a copy of the (single) Excel file through the normal assignment process (Paper and electronic). Save the file as: Assignment5_your_name: example Assignment5 – Dan Wolf.doc. Place this in the class folder with your name.Assignment #5.1Title PageStart with a blank worksheet and name it “Cover Page”.Create a cover page that contains the following:A title “SET-105 – Technical Computing”A subtitle “Assignment #5”A picture.Your nameYour email addressThe dateUse different font and font sizes as well as any other Excel features (like bold or colors) to make the cover page look professional (it will also show off your Excel skills).The cover page should print cleanly on one page.The rest of the assignments that you do should each be contained in this file and each should be contained on its’ own worksheet.Format each worksheet so that it prints in a neat and efficient manner. Use Portrait/Landscape orientation, margins, gridlines, Fit-To-Page scaling, and other print/display options. Each worksheet may have different format options.Assignment #5.2Tensile TestingIn this project you will use Microsoft Excel to produce a Lab sheet for an Engineering Materials Lab Sheet. Starting with a blank worksheet, use equations to produce the data for the blanks shown in the data sheet.The next page contains Table 5.2 which contains the technical description of the table data. Unfortunately, it was written in Dutch so you will have to use Google Translate to convert it to English.Rename the worksheet to “Tensile Table”.Include the translated English version of the Tensile Equation text on the worksheet.EMP Data:SteelCRCSAluminumBrassOriginal Diameter, in.0.3130.3150.3130.317Original Area, sq. in.Maximum Load, lbs.9500930044005100Ultimate Tensile Strength, psiBreaking Load, lbs7000680038004500Original Gage Length, in.2.0022.0012.0032.002Final Gage Length, in.2.2822.2582.2452.216Percent Elongation, %Break Diameter, in..208.220.239.232Area at Break, sq. in.Area Reduction, %Table 5.2 – Description of the Tensile Equation:Note: This text is in Dutch and must be translated to English. Use Google Translate to convert the following text to English (unless you can read Dutch).Oppervlakte wordt gemeten met behulp van de standaard vergelijking π R2.Taaiheid is een maat voor het vermogen van een materiaal om plastisch te vervormen zonder te breken. De twee meest voorkomende methoden van Ductiliteit meting zijn:a. Percentage rek wordt bepaald door het instellen van een gage lengte (meestal 2 inch) op een monster vóór het laden en na treksterkte falen meten van de uiteindelijke afstand van deze gage merken. Dan is een percentage rek wordt berekend.Rek (%) = Final Lengte - Original Lengte * 100 -------------------------------------------------- Original Lengteb. Percentage specifieke vermindering berekend door er de twee uiteinden van het gebroken monster elkaar en de diameter op het scorebord. Bereken de oppervlakte Bij de rust op dit punt van de breuk. Dit laatste gebied wordt vervolgens vergeleken met de oorspronkelijke oppervlakte van het monster en een percentenvermindering gebied wordt dan berekend.% Reductie in Area = Original Area - Final Area * 100 ------------------------------------------ originele AreaTreksterkte of treksterkte is de maximale trekkracht gedeeld door het oorspronkelijke exemplaar dwarsdoorsnede. Het is een van de belangrijkste eigenschappen bepaald door trekproeven.Treksterkte (psi) = maximale belasting (lbs.) ----------------------------------- Originele Area (in 2)Assignment #5.3Excel Grade BookUse a Microsoft Excel spreadsheet to simulate a grade book for the course Technical Computing for the following students. This table should be on a Worksheet named “Class Data”. All calculations for this entire assignment should be located on this worksheet.ProjectsExamsStudent12345678910Mid-TermFinal?????????????Stewart, L.182624321630593726189087Stein, E.162424331730603222189492Tolano, W172023401630523430208189Parker, C.122824402029553627198296Shadid, A.202625341930553630177384Lokay, J.152024311328513130168972Lyall, S.132425391730594024209298DiRita, L.2024224018305840302010096Wilson, S.173020381926533225158178Rohr, J.172925371430554027149397?????????????Total20302540203060403020100100This table should be on a Worksheet named “Class Summary”. It should retrieve the data from the “Class Data” worksheet so there should not be any calculations on this page.ProjectExamFinalStudentGradeGradeGradeStewart, L.Stein, E.Tolano, WParker, C.Shadid, A.Lokay, J.Lyall, S.DiRita, L.Wilson, S.Rohr, J.?Averages:The spreadsheet should include the following:For each student, calculate the following:Average project grade: average of the 10 project gradesAverage exam grade: average of the 10 exam gradesStudent Final grade: 25% * Student Project grade + 75% * Student Exam gradeCalculate the averages for the Projects, Exams, and Final columns (via the average command).Any cell that has a calculation should have a very light shade of grey. Any cell that the user may change (student grades) should be shaded light green.Assignment #5.4Excel GraphingUse Microsoft Excel to graph data. Get the file named “Assignment_5 - Filterdump_Data.txt” from the class website and open it.Select ALL of the data and then copy it.Switch to an empty worksheet in Excel and paste the data into a column on left.Rename the Worksheet to “Graphs”Graph the data.Select the data in Excel.Insert | Line chart then select the first 2-D option.Put your curser on the data line that is on the graph and Left-Click twice. Change the line color to Green.Experiment with the options provided to add a chart title and some X and Y axis labels.Experiment with the other graphing charts and options and include with this assignment. Create a new Bar Chart that reflects each of the final grades that were calculated in the above Grade Book assignment. For extra credit, retrieve the data directly from the “Class Data” worksheet.For extra credit, create two different columns of data and then graph them both on the same graph.Assignment #5.5Excel DatesDates can be entered into Excel by typing them normally: Jan 1, 2014.You can then “add” dates by using an equation such as: =B4+2. This will add one day to the B4 cell and display it in the destination cell. This is better than “hardcoding” each of the 31 date titles. Starting January 1, 2014 and continuing until January 31, 2014 record the high and low temperature and then calculate the average temperature for each day. In Excel produce a spreadsheet to record and calculate the data. Then on a single embedded chart, plot temperature versus the day. There should be three series on this single chart with the series being the high temperature, the low temperature and the average temperature. Each line of your chart showing these three series should be a different color. Turn in a printed copy and save your spreadsheet in our course folder as explained on the cover sheet for this assignment.You should look up the average temperatures for the month on the internet. You may use any city or location (think beach, island, or exotic). Make sure you add the internet location where you found your data to the worksheet.Name the worksheet “Dates”.There are many other Excel date and time functions. For extra credit, try some of them out and display your results.Assignment #5.6Goal SeekingGoal Seeking is an Excel function that will calculate an unknown value based on an equation.Enter the following data on an Excel worksheet that is named: “Goal Seeking”:CD1a=22b=43c=64x=25f=22Replace the 22 in the bottom row (cell D5) with the following equation: =D1*(D4^2)+D2*D4+D3This is the quadratic equation of the form: f=a*x2+b*x+cNow let’s assume we want to know the value of x when a=3, b=6, c=9, and f=250We could perform some algebra but Excel will calculate this for us.Put the Excel cursor on Cell D5Click on the Data Tab at the top of the Excel page.Select “What-If Analysis” and then “Goal Seek” “Set Cell” = D5“To Value” = 250You can make this any value that you want.“By Changing Cell” = D4We are solving for the value of X that will result in F = 250Click on OK to see the result. If you click OK again, it will insert the new value of X into your worksheet.Set up the cells so that only two decimal places are shown.Do not delete or overwrite this quadratic equation example when you move to the next step.The volume of a sphere is given by the following equation. In a space below the quadratic equation, use the Goal Seek equation to calculate the radius ( r ) that will result in a Volume (V) of 300. Hint: You can use the Excel function pi() to produce the value of PI. Just substitute pi() in place of 3.141593.V=43*π*r3By the way – the equations defined above were done with the MS-Word equation editor. If you download the electronic copy of this assignment from the website, you can explore this a little closer.Assignment #5.7Math FunctionsPut the value of 2 in the A1 cell.Put the Excel cursor on cell B2.Click on the Formula tab at the top of the Excel page then select “Math and Trig”.Select COS and enter A1 then click on OK.This inserts the equation “=COS(A1)”in cell B1 which then shows the value of -0.41615 (the Cosine of 2 Radians).You could have also just typed “=COS(A1)” directly into the cell instead of going through the Formula tab.Try three more math functions and insert them into cells C1, D1, and E1. All these should calculate based on the value of call A1.Look in “More Functions” and then “Statistical”. Try one or two of these.Name this worksheet “Math”.Assignment #5.8Conditional FunctionsThis assignment will show you how to conditionally convert between Canadian and US Dollars. Type the following into a new worksheet exactly as shown. Name the worksheet “Conditional”. Set each of the four numeric cells to show two decimals - yours will look nicer than the example.In Cell B3, type the following: =IF(A3="USD","CA","USD")This formula says:“If the content of cell A3 is equal to “USD” then put the text “CA” in cell B3 but if A3 is NOT equal to “USD” then put the text “USD” in cell B3”Thus, this changes the text in B3 based on what you type into A3.In Cell B4, type the following:=IF(A3="USD",A4*B2,A4*B1)This formula says:“If the content of cell A3 is equal to “USD” then put the value of A4*B2 in cell B4 but if A3 is NOT equal to “USD” then put the value of A4*B1 in cell B4”Thus, this converts to either Canadian or US Dollars based on what you type into A3.Change the text in cell A3 to CA and observe the result. Note that you must use upper case!Change the fill colors to light green for the cells that can be changed (B1, B2, A3, and A4).Change the fill colors to light grey for the cells that can NOT be changed (A1, A2, B3, and B4).Create your own formula in Cell C3 that will show the text “Must use upper case” if cell A3 is “usd”Create your own formula in Cell C4 that will show the text “Must use upper case” if cell A3 is “ca”OPTIONAL ASSIGNMENTS:The total resistance of a series electronic circuit is given by the equation in Figure A:Figure AGenerate a small spreadsheet calculation that computes the total resistance of any four resistance values in series. The four resistance values must be any value entered by the user. The total resistance value must be labeled as such so that the user should not change it. Add enhancements to make it easier for the user to use and to protect against values smaller than zero or larger than 5,000,000.The total resistance of a parallel electronic circuit is given by the equation in Figure B:Figure BGenerate a small spreadsheet calculation that computes the total resistance of any four resistance values in parallel. The four resistance values must be any value entered by the user. The total resistance value must be labeled as such so that the user should not change it. Add enhancements to make it easier for the user to use and to protect against values smaller than zero or larger than 5,000,000.Something else of your choosing…CCAC SOUTH: SET-105 - Technical Computing Spring 2019Assignment #5Excel AssignmentsRevised: 9/12/2019Student Name:ObjectiveStatusComments5.1 – Title/Cover Page exists & is enhanced105.2 – Tensile Table with translation105.3 – Grade book105.4 – Graphing Filter Dump Graph5 Grade Graph5 Extra Credit – external data retrieval i.e. get data from the other worksheet Extra Credit (graph options) Extra Credit (2 different graph lines)5.5 – Dates10 Extra Credit (other date functions)5.6 – Goal Seeking105.7 – Math Functions105.8 – Conditional Functions10All pages printed are neat & efficient2Filename is correct2Paper screen print of each worksheet2Each worksheet is named correctly2All files are on the S-Drive2Other Optional Features:10Optionals:Total Points:100 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download