190 likes | 343 Vues
Charts and Graphs. William Klingelsmith. Reminders. MyITLab Lesson A due by the end of the day today MyITLab Lesson B and Homework 1 due by the end of the day Friday
E N D
Charts and Graphs William Klingelsmith William Klingelsmith
Reminders • MyITLab Lesson A due by the end of the day today • MyITLab Lesson B and Homework 1 due by the end of the day Friday • When emailing questions about MyITLab, please include the component (Training or Post-Test) and the Step ID (appears in the format ‘Step 1.5.2’) William Klingelsmith
Charts and Graphs • Excel has several powerful features to visualize your data in different forms of charts of graphs • Terminology • Data point: a single entity that is going to be graphed or charted. • A single statistic for a single employee • Data series: a collection of related data points. • A single statistic for all employees William Klingelsmith
Charts and Graphs • It is always important to think about what type of data you’re trying to plot before you begin construction of a chart or graph • Some chart types do not work for certain types of data • Pie charts work well for showing relationships • Bar/Column charts work well for discrete samples of data • Line charts work well for continuous data William Klingelsmith
Charts and Graphs • Let us first create a simple pie chart that shows us information about the employee’s genders • Select cell range A25:A26 and B25:B26 • Move to the insert tab • Choose Pie, and then 2D Pie Chart William Klingelsmith
Charts and Graphs • Our chart appears to be logically oriented, but it is missing a lot of helpful information • Labels • Chart label • Small chart • Data labels can be added to the individual pieces of the pie by right-clicking on the pie graphic and choosing Add Labels. These labels can be further modified by right clicking on them again. • All chart labels can be added/removed through the layout tab. • We can move the chart to its own sheet by right clicking and choosing Move Chart. William Klingelsmith
Charts and Graphs • Let’s create a column chart next. • We wish to plot current salaries by each employee • A3:A14 and E3:E14 is the cell range we wish to use • Insert->2D Clustered Column Chart • Move chart to a new sheet and add appropriate labels William Klingelsmith
Charts and Graphs • We made an error in our last chart. We should have plotted both current salary and previous salary • We need to modify our existing chart to change this. William Klingelsmith
Charts and Graphs • Right click in the whitespace on your column chart and choose Select Data. • Inspect the existing series • Create a new series using the data in the Previous Salary column (F4:F14) William Klingelsmith
Charts and Graphs • Let’s now plot the average employee salary over a 13 year period. • Switch to the Average Employee Salary sheet • Select cell range A1:B14 • Insert->2D Line Chart William Klingelsmith
Charts and Graphs • There are several issues with this chart • Can you name at least three? • We can fix all the problems in this chart by returning once more to the Select Data dialog • Respecify horizontal axis values • Fix or Remove any erroneous series • Axis bounds can be adjusted by right clicking on the Y-Axis and going to Format Axis William Klingelsmith
Trendlines • Trendlines represent the shifts in data values (commonly over time) which can be positive or negative • Positive trend could be the increasing value of stock prices • Negative trend could be the amount of oil existing in the world at different times in history
Trendlines • Trendlines always appear in common mathematical constructs • Linear • Exponential • Logarithmic • The ability to choose the correct trendline for your data is important. • “Does the trendline type actually fit the data?” • “Does the trendline make sense when put into context of the data?”
Trendlines • Create a 2D line chart using the cell range B4:C27 • To add a trendline to your graph, first right click on the series line(the blue line in the graph in this case) and choose “Add Trendline”
Trendline Dialog Trendline Types Forecasting Options Intercepts, R2, and Equations
Trendlines • Pick an appropriate Trendline Type • In this case, the most appropriate type is exponential • Moore’s Law!
Forecasting • Trendlines have the powerful ability to make predictions using current data • Forecast 5 periods into the future • Roughly 5 years
Confirming your Choices • R2 Value • The R^2 value or “Coefficient of Determination” is a number between 0 and 1(0% and 100%) which represents the amount of existing data values which fit in your statistical model • Try to get your R2 value as high as possible