Study the PowerPoint presentation – Excel C1. Graphing curves with multiple sets of y values2. Graphing curves with multiple sets of x valuesTableLookupEGR 120 Due date: _________________ Introduction to Engineering File: N120H8M Homework Assignment #8 (Excel C) Reference: Read Chapters 10-13 in Thinking Like An Engineer – An Active Learning Approach, 2nd Edition by Stephan. Study the PowerPoint presentation – Excel C Example: Using Lookup Tables in Microsoft Excel Example: Using Histograms in Microsoft Excel Assignment: For each problem below: • Include your name, course number, course title, assignment number, problem number and brief instructions. • Include any given data in tables. The tables should include lines, complete table headings, and centered data with the provided number of digits. If any equations are used to calculate values in table, show sample Excel equations below the table. • Graphs should include appropriate formatting. • Follow any other instructions given with each problem. As with previous Excel assignments, use one Excel file to store all parts of the assignments by placing each part on a different sheet (Sheet1, Sheet2, Sheet3, etc – renamed as Problem 1, Problem 2, Problem 3, etc) within the file. Submit the single Excel file using Blackboard. Warning: Your assignments must be your own work. You can ask other students questions, but sharing files is cheating. If any evidence of copied files is discovered, all parties involved will receive grades of 0 and may be subject to further disciplinary action. 1. Graphing curves with multiple sets of y values Repeat (or copy) the table of Problem 6 in Homework #6 (the table of values for f, XC, XL, Z, and p.f.). Form a single graph showing XC vs f, XL vs f, and Z vs f. Include a legend as shown below. Use a scatter plot with a smooth curve (no trendline is needed). The final graph should look like the graph shown below (with your name instead of John Doe). Use a log scale on the x-axis. 0.00E+001.00E+032.00E+033.00E+034.00E+035.00E+036.00E+037.00E+038.00E+039.00E+03100 1000 10000 100000Ohms frequency, f (Hz) XC, XL, and Z versus frequency John Doe - EGR 120 XCXLZPage 2 2. Graphing curves with multiple sets of x values Shown below are voltage and current values for two types of electrical devices called diodes. One of the diodes is made of germanium and the other is made of silicon. It is common to graph these curves with common current values and with current on the y-axis. • Form a spreadsheet with the table below and with a graph like the one shown below (use linear scales for both axes). • Add exponential trendlines for each curve as shown. Germanium Diode Silicon Diode Current (mA) Voltage (V) Voltage (V) 1 0.473 0.592 2 0.491 0.627 3 0.502 0.648 4 0.509 0.663 5 0.515 0.674 10 0.532 0.710 20 0.550 0.746 30 0.561 0.766 40 0.568 0.781 50 0.574 0.793 100 0.592 0.828 Ge: I = 1E-08e38.838V R2 = 0.9999 Si: I= 1E-05e19.472V R2 = 1 0204060801001200.000 0.500 1.000Current, I (mA) Voltage, V (V) Current vs Voltage for Si and Ge Diodes John Doe - EGR 120 GeSiExpon. (Ge)Expon. (Si)Page 3 3. Wind Energy Table and Column Chart • Go to the Department of Energy web site at www.doe.gov • Select Trends and Prices and then select Annual Energy Review (or else search for Annual Energy Review). • Under Tables and Graphs, select 8. Electricity • Select Table 8.2b – Electricity Net Generation (pdf file under Tables) A) Take data from the Annual Energy Review and use it to create a table in Excel like the one below. Fill out the two columns on the left with data from the table beginning in 1998 through the most recent year reported. Use a formula in Excel to calculate the % of wind power. Year Wind Energy for Electricity Generation (Billion kW⋅h) Total Energy for Electricity Generation (Billion kW⋅h) % of Electricity Energy Generated by Wind 1998 3.0 3457.4 0.087 1999 4.5 3530.0 0.127 2000 5.6 3637.5 0.154 most recent B) Form a column chart from the Excel table created (as described above) and create a graph of % of Electricity Energy Generated by Wind. 4. Pie Chart Create a pie chart with the following data. • Include a legend. • Include percentages, discipline titles, and the number of degrees next to each pie slice. Each percentage should include 1 digit after the decimal point. • Include the table headings indicated below 2004 Bachelor's Degrees Awarded by Discipline (Total: 72,893) Source: American Society of Engineering Education Discipline B.S. Degrees Awarded Mechanical 14,182 Electrical 12,500 Computer Science 9,156 Civil 8,142 Computer 5,838 Chemical 4,801 Industrial/Manufacturing 3,790 Electrical/Computer 2,700 Other 2,488 Aerospace 2,232 Biomedical 2,019 General Engineering 1,138 Metallurgical & Materials 817 Agricultural 601 Architectural 590 Environmental 576 Eng. Science & Physics 501 Engineering Management 302 Petroleum 233 Nuclear 202Page 4 5. Statistics and Sorting Download the file WorldPopulationTable.xls from the course Blackboard site or from http://www.tcc.edu/faculty/webpages/pgordy/Egr120/ Sort the data in several ways to answer the following questions: A) Which 5 countries had the lowest population in 1999? (Not counting 0, --, or NA) B) Which 5 countries had the highest population in 2006? C) What was the average population of all countries in Africa in 2004? D) What was the average population of all countries beginning with the letter B in 2005? E) How many countries had a population between 40 million and 60 million in 2003? F) Which 5 countries had the largest percentage growth in population from 2005 to 2006? Hint: Add a new column labeled Growth using (2006 population-2005 population)/(2006 population)*100. 6. Histogram and Cumulative Distribution Function An Electrical Engineering student bought a pack of twenty 10 kΩ resistors from a local electronics store. The resistors have a 10% tolerance, so all values should lie between 9 kΩ and 11 kΩ. The student measured each value using an ohmmeter and the results are shown below. A) Generate a histogram (and the bin/frequency) table based on the ranges specified below. Note: the form shown for the bounds are not in the correct format. So add an additional column for bin values. Be sure to check “Chart Output” and “Cumulative Percentage” to show