Excel is designed to work with numerically oriented data ...



Excel Tutorial #4This lab focuses on using Excel data to create charts and graphs. It’s also designed to provide additional exercises with formulas, functions, and cell referencing that mixes the relative and absolute methods.Statistical FunctionsThis lab uses the statistical functions of average, median, and standard deviation.Average – also called the mean, is a sum of numbers divided by the count of those numbers. For example, if a list of numbers includes 5, 8, 10, 13, and 24, the sum is 60 and the count is 5. Thus the average is 12 (60 divided by 5).Median – the middle value in a series of numbers or that number which has as many numbers greater than it as less than it. For example, if a list of numbers includes 5, 8, 10, 13, and 24 then the median is 10 since it has as many numbers in the series less than it as greater than it. Standard Deviation – a statistical measure of the variation between the numbers in a series. For example, the series 8, 10, 12, 14, and 16 has the same average as the series 5, 8, 10, 13, 24 but has a smaller standard deviation since the high and low numbers differ by only 8 while the high and low differ by 19 in the second series.Mixing Relative and Absolute Cell ReferencesWhen copying formulas, the Excel default is to adjust the cell reference as you copy. This is known as a relative reference and is usually the desired choice when copying a formula. If you copy a cell with a reference to B5 to the next row down, the cell reference in the new row will automatically be adjusted to refer B6. Examples of this will be seen later. Although relative referencing is usually the right choice, at times, you don’t want Excel to adjust your cell references as you copy. If you want Excel to leave cell references unchanged when copying a formula, you use what is called an absolute reference. An absolute reference is created when you preface the row or column designation with a $. The cell reference $D$5 will remain the same no matter where you copy the formula. A mix of relative and absolute references such as $D5 or D$5 is also allowed. A dollar sign in front of a column reference prevents Excel from adjusting the column reference when the formula is copied horizontally across the columns. Thus the reference $D5 will change to $D6 when copying it to the next row down but will remain $D5 when copying it to the next column over. A dollar sign in front of a row reference prevents Excel from adjusting the row reference when the formula is copied vertically down the rows. The reference D$5 will remain D$5 when copying it to the next row down but will change to E$5 when copying it to the next column over. Prefacing any row or column reference with a dollar sign causes Excel to hold that reference constant no matter where it’s copied in the worksheet.Tutorial StepsThis worksheet is designed to help analyze which of four possible towns is best suited for the location of a new manufacturing facility. Four criteria are considered in the decision process and each of the four possible candidates has been assigned an Unweighted Score for each criterion, giving us 16 unweighted scores found in the cells ranging from B7 to E10. The four criteria are:Market SizeLabor PoolLocal TaxesOperating CostsOf these 4 criteria, Operating Costs is considered more vital than the others and thus has been assigned a higher weight as shown in column G. A simple average of the unweighted scores will not yield the desired result as it values each of the criteria equally. A weighted average approach is required to calculate the best location. Weighted scores must be calculated for each location using the unweighted scores found in cells B7 to E10 and the weighting factors found in column G. Weighted scores in cells B14 to E17 are computed by multiplying each unweighted score by the corresponding weight factor found in column G. For example, the weighted score for Waukegan market size (cell B14) is computed by multiplying the unweighted market size score for Waukegan (cell B7) by the weight assigned to the market size criterion (cell G7). The weighted score for Waukegan labor pool (cell B15) is computed by multiplying the unweighted labor pool score for Waukegan (cell B8) by the weight assigned to the labor pool criterion (cell G8). Your task is to provide the formulas to compute each of the weighted scores in B14 to E17. These formulas should not be entered individually in each cell. Instead, enter one formula in cell B14 that can be copied to the other cells in the B14 to E17 range and still yield the correct results. A mix of relative and absolute cell references is required to accomplish pute Weighted Scores Using a Mixed ReferenceClick on the Site tab at the bottom of the worksheet if necessary.Enter the mixed reference formula in cell B14 to compute the weighted average score for Waukegan’s market size.Drag the fill handle for the formula in cell B14 to cells C14 through E14.Highlight the formulas in cells B14 through E14 and drag the fill handle down to rows 15, 16, and 17.Verify the copied formulas yield the correct results. If not, return to step 2 and revise the formula in cell B14 so it yields the correct result when copied.Use the sum function to compute the total of weighted scores for each location on line 18. The results on line 18 should be 5.6, 5.2, 6.2, and 6. If not, go back to step 2, revise the relative and absolute references in cell B14, then repeat steps 3, 4, and 5 once again. Create a Column Chart Showing the Results of the Site AnalysisAdd a new worksheet for the Site Analysis ChartPosition the mouse to the Site tab at the bottom of the worksheet.Right click, then click the Insert option. This opens the Insert dialog box. Double click on the Chart option.A new worksheet tab, Chart1, appears and is automatically selected. The Design tab is selected and Chart Tools appears above the Design tab.Set the overall design of the chart.Click Change Chart Type in the Type group on the Chart Tools Design tab.Choose the 2nd item from the first row, shown as Stacked Column, and click Ok.Select the data to appear in the chart.Click the Select Data button in the Data group on the Chart Tools Design tab. The Select Data Source dialog box appears.Click the Site tab at the bottom of the worksheet to display the Site worksheet.Use the mouse to select cells A13 through E17 in the Site worksheet. The cells selected appear in the Chart data range box of the Select Data Source dialog box.Observe that the decision criteria (Market Size, Labor Pool, etc.) are shown with the Legend Entries and the locations being evaluated are shown as part of the horizontal (X) axis. Click Ok to accept.Note: If the height of the four bars do not look like the example on the next page, you should click the Switch Row/Column button in the Data group.Enter the chart title.Click the Chart Title text shown above the bar chart. A box should appear around the text.Enter the new chart title Site Analysis Prepared by Student Name in the formula bar. Enter your name as the Student Name.Select the text in the chart title and change the font size to 24 point bold.Verify the towns appear on the horizontal (X) axis at the bottom of the graph.If the town names do not appear, return to step 3 and re-select the data range. Be sure to select row 13 as the first row in the range. Describe the horizontal (X) and vertical (Y) axis.Click anywhere in the white space of the chart. A large plus sign should appear in the upper right corner of the chart.Click the plus sign in the upper right. The title Chart Elements should appear with a series of check boxes beneath it. Click the Axis Titles check box.Axis Title should now appear at both the bottom (X axis) and left side (Y axis).Click the text Axis Title found at the bottom and replace this text with Locations.Click the text Axis Title found at the left edge and replace this text with Weighted Scores.Display the values from the Site worksheet inside the columns chart.Click the plus sign in the upper right to re-display the Chart Elements check boxes. Click the Data Labels check box.The value that the weighted scores contribute to each column should now be displayed. Each criteria of the weighted score is shown as a different color. The column chart just created is shown as the Chart1 tab at the bottom of the worksheet. Right click on the Chart1 tab, select the Rename option and enter Site Chart as the new name for this worksheet.Now we’ll work on improving the visual presentation of our chart.Choose a chart styleClick the More button for the Chart Styles group on the Chart Tools Design tabChoose the final style (Style 11)Click the plus sign in the upper right to re-display the Chart Elements check boxes. Click the Legend check box to show each evaluation criteria and its associated color.Change the fill color of the legendRight-click the legend at the right showing the evaluation criteria.Click the Fill button and select a color. Be sure to select a color not currently used in the legend.Change the texture of the chart area.Right-click in any area inside the chart but outside the columns.The resulting short cut menu should display the text Plot AreaClick the Fill buttonClick the Texture buttonChoose the Stationery option from the fourth row and fourth column.Change the background color of the label area outside the chart.Right-click the area outside the chart where the title, labels, and axis descriptions are foundClick the Fill button and select a color.This concludes the chart setup for the Site Analysis worksheet. Your chart should look similar to the example below. Be sure to save your work at this time. Review StepsCompute grades for a grade book. Then create a bar chart to graphically display the distribution.Click on the Roster tab at the bottom of the worksheet.Click on cell C3 and replace Student Name with your name.Be sure to save your work from the previous section before proceeding further. Compute Averages, Weighted Averages, Mean, and Standard DeviationCompute the unweighted student averages in column J. Use the average function to compute an average of the scores (columns C thru I) for the first student. Once the average formula has been set up for the first student in cell J6, copy that formula down to row 14 to complete the average computation for each pute the weighted average in column K in the following manner.Create a single formula that multiplies each individual score by the weight assigned to that score and adds the weighted scores together.For example, multiply Bugs Bunny’s Labs score in cell C6 by the weight for lab scores found in cell C16. Then add the result of Bugs Bunny’s Homewk score multiplied by the weight for homework scores found in cell D16. Continue this for all scores in columns C through I.Use parentheses to group operations logically. For example, the formula for Bugs Bunny’s weighted average for only labs and homework would be =(C6*C16)+(D6*D16). Complete this formula so it sums all of Bugs Bunny’s scores, not just the labs and homework. Note that the example shown here does not utilize the mixed reference discussed in the next point (d).Be sure the formula you set up in cell K6 computes weighted averages for other students correctly when copied to other cells in the K7 to K14 range. This requires the reference to row 16 be held constant while allowing the other rows and columns to adjust as the formula is copied. A mix of relative and absolute cell references is required to accomplish this.Note that a grade appears in column L once the weighted average in column K has been computed.Use the Average function to compute a mean score on line 18 for each type of score in columns C through K. See the solution file for how this should appear.Use the Median function to compute a median score on line 19 for each type of score in columns C through K. See the solution file for how this should appear.Use the Stdevp function to compute a standard deviation on line 20 for each type of score in columns C through K. See the solution file for how this should appear.Create a Bar Chart Showing the Results of the Site AnalysisAdd a new worksheet for the Grade ChartUse the mouse to select the Weighted Average cells K6 thru K14Position the mouse to the Roster tab at the bottom of the worksheet.Right click, and then click the Insert option. This opens the Insert dialog box. Double click on the Chart option.A new worksheet tab, Chart2, appears and is automatically selected. The Design tab is selected and Chart Tools appears above the Design tab.Click the Switch Row/Column button in the Data group. The bars should now appear wider and in different colors.Set the overall design of the chart.Click the Change Chart Type button in the Type group on the Chart Tools Design tab.Select the Bar option listed on the left side of the dialog box and click Ok.Select the student name to attach to each bar in the chart.Click the Select Data button in the Data group on the Chart Tools Design tab. The Select Data Source dialog box appears.Click the Series1 text in the Legend Entries section of the Select Data Source dialog box.Click the Edit button in the Legend Entries section to display the Edit Series dialog box.Click the Roster tab if the Roster worksheet is not displayed.Use the mouse to select the name Bugs Bunny in cells A6 and B6.Click Ok to accept the Series Name selection and close the Edit Series dialog box. The name Bugs Bunny now appears in the dialog box.Repeat steps b, c, d, e, and f but select the Series2 entry and the 2nd name on the list from cells A7 and B7. The select the Series3 entry and the 3rd name on the list from cells A8 and B8. Continue repeating these steps until all of the Series1 through Series9 items have been replaced with names.Click the Ok button to close the Select Data Source dialog box.Enter the chart title.Click the Chart Title text shown above the bar graph. A box should appear around the text.Enter the new chart title ART290 Grades Prepared by Student Name in the formula bar. Enter your name as the Student Name.Select the text in the chart title and change the font size to 24 point bold.Describe the horizontal (X) and vertical (Y) axis.Click anywhere in the white space of the chart. A large plus sign should appear in the upper right corner of the chart.Click the plus sign in the upper right corner of the chart. Chart Elements should appear with a series of check boxes beneath it. Click the Axis Titles check box.Axis Title should now appear at both the bottom (X axis) and left side (Y axis).Click the text Axis Title found at the bottom and replace it with Weighted Scores.Click the text Axis Title found at the left edge and replace it with Students.Display the student name and score inside each bar of the bar chart.Click anywhere in the white space of the chart. Click the plus sign in the upper right corner of the chart to display the Chart Elements check boxes once again.Click the Data Labels check box. A weighted score for a particular student should appear to the right of each bar. Click the score next to the first bar. A box should appear around the score.Position the mouse to the Data Labels check box line. A right pointing triangle should appear to the right of the Data Labels check box. Click the right pointing triangle. A list of options should be shown. Click More Options from the list. A list of Format Data Labels options appears.Check the Series Name and Value boxes. Select the Inside End radio button. Click the weighted score for the next bar shown on the chart. Then repeat step g for each of the scores shown.Student name and score should now appear inside each bar. Close the Format Data Labels options by clicking the X in the upper right corner. The bar chart just created is shown as the Chart2 tab at the bottom of the worksheet. Right click on the Chart2 tab, select the Rename option and enter Grade Chart as the new name for this worksheet.Improve the visual presentation of your bar chart with the tricks and techniques used earlier for the Site Chart. Experiment with different colors, textures, gradients, and color combinations to achieve a more dramatic effect.Finally, remove the “1” that appears on the vertical axisClick the “1” that appears next to Students on the vertical axis. A long, narrow box running vertically along the entire chart should be shown around the “1”.Press the delete key to remove this box and its contents. This concludes the chart setup for the Roster worksheet. Be sure to save your work and compare your results to the solution files provided with the assignment. ................
................

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

Google Online Preview   Download