EGR 120 Introduction to Engineering File: N120L12M Lecture #12 (Excel Lecture C) Reading Assignment: Chapter 14 in Engineering Fundamentals – An Introduction to Engineering, 3rd Edition, by Saeed Moaveni. Example: Using Lookup Tables in Microsoft Excel Example: Using Histograms in Microsoft Excel Statistical Functions in Excel Create a list of values (Ex: Recent gasoline prices) and determine the following: Mean, Max, Min, Median, Mode, Number, and Standard Deviation Histograms Use a histogram to find out how many of the values in the list of gas prices fall into selected ranges (perhaps in 10 cent increments). Note: The BIN values contain the upper limits for each range. Example BIN: BIN Comments 2.40 Value < 2.40 2.50 2.40 < Value < 2.50 2.60 2.50 < Value < 2.60 2.70 2.60 < Value < 2.70 Perform a histogram using TOOLS - DATA ANALYSIS - HISTOGRAM from the main menu (if DATA ANALYSIS does not appear under TOOLS, first pick TOOLS - ADD-INS and then check the box next to ANALYSIS PACK). See the example: Using Histograms in Microsoft Excel Lookup Tables and Lookup Functions Use a lookup table and lookup function to calculate the postage and handling charges to mail packages using a rate schedule as follows. The form of the LOOKUP function is as follows: LOOKUP(data value, range of comparison values, range of values to use) Create a spreadsheet with the cost of several items in one column and use a lookup table and lookup function to determine the shipping charge according to the table provided below. Purchase Amount Shipping Charge Up to $10.00 5% Up to $25.00 4% Up to $50.00 3% Up to $75.00 2% Up to $100.00 1% See the example: Using Lookup Tables in Microsoft ExcelPage 2 Graphing curves with multiple x-series Excel can easily to used to graph multiple curves. The curves may have • Different y-values and common x-values (perhaps most common) • Different x-values and common y-values • Different x-values and different-y values Example: Graph the following information using Excel (three curves on one graph). x y1 y2 y3 0 10 13 18 2 20 26 36 4 30 39 54 6 40 52 72 8 50 65 90 10 60 78 108 12 70 91 126 Example: Graph the following information using Excel (three curves on one graph). x1 x2 x3 y 0 1 0.5 10 2 3 2.5 20 4 5 4.5 30 6 7 6.5 40 8 9 8.5 50 10 11 10.5 60 12 13 12.5 70 Example: Graph the following information using Excel (three curves on one graph). x1 x2 x3 y1 y2 y3 0 1 0.5 10 13 18 2 3 2.5 20 26 36 4 5 4.5 30 39 54 6 7 6.5 40 52 72 8 9 8.5 50 65 90 10 11 10.5 60 78 108 12 13 12.5 70 91 126Page 3 Other types of graphs: x-y (scatter) graphs are commonly used in Engineering, but several other types of graphs are also very useful at times. Excel has a large variety of graph types available. Several are explored below. Line charts versus Scatter Charts Scatter charts are used most commonly to graph measured x,y data. A common error is to use a line chart instead of a scatter chart. Line charts are intended for cases where the data on the x-axis is non-numeric, so the x values are simply ignored and are spaced evenly. Line charts work well when the x-axis shows days of the week or months, but they can give a misleading impression with numeric data. Also note that since line charts treat the x-axis data as non-numeric, Excel does not give the option of using a log scale for the x-axis. (What is the log(January)? ) Example: Graph the data below using both a scatter chart (with a smooth curve added) and a line chart. The scatter chart will give the correct graph. The line chart will be misleading. x y 1 10 2 20 20 30 Example: Graph the following data using a: • Line chart • 3D line chart • Bar chart • 3D bar chart • Column Chart • Pie chart • 3D pie chart • Donut College Major Number of Students Engineering 120 Physics 50 Math 40 English 150 Sociology 250 Example: Graph the following data using a(n): • Area chart (what is meant by the entire area?) • Line chart • 3D bar chart Month Labor Materials Operational Expenses Jan $8500 $2400 $4000 Feb $8000 $2600 $3975 Mar $9000 $2800 $4100 Apr $9000 $2950 $4075 May $8800 $3075