Course Work Test

Course Work Test You are submitted the two excel files and completed word responses in the exam link by the end of class time. Be sure to click add another file so you can add all files before you click submit. (25 pts) Using the completed file named Expenditures.xlsx from Chapter 2 (rename to ExamExpenditures.xlsx. Create another pie graph using the Quarter 1, Quarter 2, Quarter 3, Quarter 4 titles and the Average Expenditures amounts. Move the chart to its own sheet named Quarters Pie. Remove the legend Add data labels that show the Category Names and the Percentages Format the data labels to be legible and neatly inside the pies Add a title to the chart name Quarterly Average Expenditure (25pts) Using the completed file named Parking.xlsx from Chapter 3 (rename to ExamParking.xlsx Add the following functions and formatting to all worksheets. Group the April – June worksheets so they have the same outcome. Insert two blank rows between row 11 12 In cell A11 type Minimum. In cells B11-F11 use the appropriate function for the data in rows 5-8. In cell A12 type Maximum. In cells B12-F12 use the appropriate function for the data in rows 5-8. In cell A13 type Average. In cells B13-F13 use the appropriate function for the data in rows 5-8. Format range A11-F13 any way you want, but make sure it is consistent on all worksheets. (30 pts) Using the Garden.xlsx file you completed in Chapter 4 as a reference. Answer the following questions with at least 25 words: Explain how the If function works (ie. what are the parts to the function and what do they do)? How did it help analyze the data in the Garden.xlsx file? The If function allows displaying a specific value if a particular logical test is correct; and another value if the test is wrong. The first argument of the function is the logical test. It can be any test whose answer is TRUE or FALSE. The second argument of the function will be the value that will be in the cell if the test is true, and the third argument will be the value returned in the cell if the function is false. for instance, =IF(B4C4;Inferior;Not inferior) will test if the value in the cell B4 is inferior to the one in the cell C4. It will return the word â€Å"Inferior† if the condition is respected and â€Å"Not inferior† otherwise. The If function is very useful to determine if a reorder should be placed. What does conditional formatting do? Why is it useful? Conditional formatting allows to format cells according to specific criteria. For instance, in an excel spreadsheet analyzing profits and losses of a company; we could automatically color losses in red and profits in green. Conditional formatting is very useful when working with large sets of data and when the values change often. The color formatting will then change automatically; we do not have to update it manually. How do data bars or spark lines work? Why is it useful? Data bars and spark lines analyze a set of numerical data as input and generate a graphical representation of it in an output cell of choice. Data bars and spark lines are very useful because they help intuitively see how the data is evolving. Moreover, when working with large data sets whose value frequently change, data bars and spark lines are updated automatically. One does not need to generate graphs each time has the spark lines can be dragged down a long table. They are useful for instance for visualizing the change in the profits of a company over time. (20 pts) Using the Payroll.xlsx file you completed in Chapter 6 as a reference. Answer the following questions with at least 25 words: Explain how the Lookup function works (ie. What are the parts to the function and what do they do)? How did it help analyze the data in the Payroll.xlsx file? The VLOOKUP function allows to lookup a value in a table and return a value situated in the same row (but from a column of your choice). The first argument is the lookup value; that is the value we are looking for. The second argument defines the size of the table. The third argument is the column number of the aforementioned table from which the matching value should be taken. A fourth value allows to find an exact match or not and is called Range lookup, but it is not mandatory to use the function. VLOOKUP is very useful when working with big databases of clients and needing to return a specific value. It could be used for example to return the phone number of a client when typing his name in an input cell. VLOOKUP was very useful to process the data from the â€Å"Employees† spreadsheet in Payroll.xlsx. We used Named Ranges in order to efficiently use the advanced functions in Chapter 6. What are Named Ranges and why did named ranges work better than cell references when we copied the functions? Name ranges allow giving a specific name to one or many cells related to each other. For instance, we can name the cells C14:C17 â€Å"Tax_Rates†. Name ranges have 2 main advantages:they allow faster formula typing and faster formula reading. For instance, the formula =AVERAGE(Tax_Rates) is much more readable and intuitive than =AVERAGE(C14:C17). Named ranges worked better than cell references when copying functions because by default named ranges are automatically fixed, equivalent to absolute cell references ($). This makes dragging most formulas very convenient.