cis 122l

WEEK 9 ASSIGNMENTS

STARTS: Thursday, April 4, 2019

DUE: Midnight Wednesday, April 10, 2019

Substituting for the normal 50 pages of reading in the textbook, this week there are only 6 pages in the book and 20 pages of reading in this document. This paper includes 2 new topics not found in the textbook and 7 review topics that often cause problems for some students.

Please complete the 5 parts of the Week 9 assignments as listed below. Use the link provided on the Assignments page to upload your three completed files: Agape Center, Exercises, and Book Club.

Assignment #1 In Module 12, read pages EX752 – EX754, EX757, and EX776 – EX777 and complete the steps in the buff colored sections. On page EX776 you need the Paris.xlsx workbook with the Income Statement sheet active. Do not submit that workbook or the PDF file.

Assignment #2 Pages EX 781 - EX 782 Case Problem 2 – Complete steps 1, 2, and 6-11 only. Save the file with your name and Agape Center (e.g. SmithJ Agape Center). Submit the file using the provided link on the Assignments page by the due date.

Notes for Module 12 Case 2:

#6 “Add the following names as the workbook authors…” means add to the document properties, not to cell B3 on the Documentation sheet. If you press Enter after typing a name, the Check Names dialog box will appear. Merely cancel it each time.

#7 To find the Status document property see step 7 on page 753. It is not included on the Summary page of properties in the dialog box. Additionally, the Status that appears on Backstage view is not the same as the Status that appears in the list of Custom properties.

Assignment #3 Read the nine instructional topics below and complete the steps in the buff colored sections using the ReviewAndNewTopics.xlsx workbook. Do not submit that workbook.

TOPIC 1 (NEW): Outlining

When a worksheet contains data with levels like month, quarter, and year, formulas often summarize the lower levels of data (months) to calculate the higher levels (quarters). Also the quarters may be summed to calculate the year. Examine the Quarters sheet in ReviewAndNewTopics.xlsx where the months are summed to calculate each quarter and the quarters are added to total the year.

Excel’s outline command analyses the formulas on a sheet to determine the different levels of calculations and then groups similar levels of data together. It also provides buttons for displaying the various levels of data quickly.

1. Open the ReviewAndNewTopics.xlsx workbook and make the Quarters sheet active.

2. Select B4. (Notice you only need to select a single cell within the block of data.)

3. On the Data tab, in the Outline group, click the drop-down arrow for the Group button and pick Auto Outline.

The outline has been created and buttons appear in the area above the column headings. The buttons are for quickly displaying the different levels of data. Since there are three levels of detail (month, quarter, and year), there are three buttons numbered 1, 2, and 3 at the left edge of the area above the column headings. The 3 button displays all three levels of data. The 2 button displays only the two highest levels of data (quarters and year). The 1 button displays only the single highest level of data (year).

4. Click the 1 button to display only the year results.

5. Click the 2 button to display the quarter and year values.

6. Click the 3 button to display all three levels of data (months, quarters, and year).

Should you want to hide only the months in a single quarter, like April, May, and June which make up Quarter 2, there are minus sign (Hide Detail) buttons. When clicked, a minus sign button will change to a plus sign (Show Detail) button so the hidden data can be redisplayed. (See pages EX295-EX296 for additional detail.)

7. Click the minus sign button that is above the header for column M to collapse the July-September months.

8. Click the minus sign button that is above the header for column Q to collapse the October-December months.

9. Click the button to hide the January-March months.

10. Click the minus sign button above column R to see what it does.

11. Click the plus sign button that is above the header for column M to expand (redisplay) the July-September months.

12. Click the 3 button to expand all months.

13. On the Data tab, in the Outline group, click the drop-down arrow for the Ungroup button and pick Clear Outline.

14. Switch to the Schools sheet.

15. Use the Auto Outline command in the Group button’s drop-down menu to create an outline for the ECAP data.

16. The buttons above the column headers control the display of the columns. Click the 2 button so only the two School columns and the University column (D, G, and H) are displayed.

17. Redisplay all data with the 3 button.

The Schools sheet has levels of calculations in both vertical and horizontal directions. The schools are summed across the columns. The states are summed down the rows. Thus, Excel found it could outline both horizontally and vertically and supplied buttons to control the display both ways.

To the left of the row numbers is another set of buttons. These buttons control the display of the levels of data in the rows. They work the same as the buttons for the columns except for the direction.

18. Click the 1 button at the left edge of the screen to hide the second level of row data (NY, NJ, CT, and Other).

19. Click the 2 button to bring back the state’s data.

20. Click both 1 buttons.

21. Save the workbook.

TOPIC 2 (REVIEW): Subtotals

This is a review topic as it was also covered on pages EX292-EX296.

In Excel, it is quite common to have a list of data with one column containing categories. That list would often benefit from being subtotaled. While you could sort the list, insert a row at each change of category, create a calculated sum (or average or count, etc.), and create grand totals at the bottom of the list, Excel has a command that will do all of that for you (except you must sort first).

1. In the ReviewAndNewTopics.xlsx workbook, make the SubtotalsByProduct sheet active.

2. Your manager wants you to get subtotals for the Commissions for each product listed in the Title column. First you must group all of the same Title together by sorting. Select G3. On the Data tab, in the Sort & Filter group, click the AZ↓ button.

3. With any cell inside the list selected (the current selection of G3 is good), on the Data tab, in the Outline group, click the Subtotal button.

4. Open the drop-down list for “At each change in:” and pick Title.

5. Open the drop-down list for “Use function:” and pick Sum.

6. In the open list for “Add subtotal to:” check Commission (if necessary). That will create the sums for the Commissions.

7. “Summary below data” should be checked so the subtotals will be placed below each group of listings rather than above. (The “Replace current subtotals” checkbox does not matter the first time the command is run and we do not want page breaks.)

8. Click OK.

Examine the result. Thirteen new rows were inserted for the subtotals at rows 8, 14, 18, 21, 23, 29, 38, 42, 48, 50, 55, 57, and 60. Sums were created in the Commission column and a label was supplied in each of those 13 rows. (“12G Memory Module Kit” in row 8 in bold type, for example.) Finally, the label and calculation for the Grand Total were created in row 61.

To make it easy to comprehend and print this subtotaled list, outline buttons (like the ones we just studied for outlining) were placed at the left edge of the screen.

9. Click the 1 button to hide all of the individual titles and the subtotals, leaving only the Grand Total showing.

10. Click the 2 button to show only the subtotals and grand total.

11. Click the 3 button to redisplay all data.

12. Click the 2 button to show only the subtotals and grand total again.

13. To the left of row 48, click the + button to show the Color Inkjet individual sales in rows 43-47.

14. Click the + button to the left of row 60 to redisplay the individual sales for Word.

15. Click the 3 button to display all data.

16. Switch to the SubtotalsBySalesperson sheet.

17. Click on any cell within the list. D5 would be a good choice.

18. You need to sort by the Invoice # as most important and the Title as second most important. (More on multiple sorts is in a later topic in this document.)

19. On the Data tab, in the Sort & Filter group, click the Sort button. (Alternatively, you could have clicked Custom Sort in the Sort & Filter button’s menu on the Home tab.)

20. In the Sort dialog box, open the first drop-down box in the “Sort by” row and pick Invoice # as the most important column by which to sort. “Sort On” should already say Values and “Order” should already be Smallest to Largest.

21. To specify the second most important column by which to sort, click the “Add Level” button. A second row appears allowing a second most important column to be selected as part of this multi-column sort.

22. Open the first drop-down box in the “Then by” row and pick Title as the second most important column by which to sort. “Sort On” should already say Values and “Order” should be A to Z.

23. Click OK. (You can sort by up to 16 columns of data, but two is all we need.)

24. With the rows sorted properly, click the Subtotal button on the Data tab.

25. Open the drop-down list for “At each change in:” and pick Invoice #.

26. Open the drop-down list for “Use function:” and pick Sum (if necessary).

27. In the open list for “Add subtotal to:” check Total Amount and remove the check from Commission so the sums will be created for the Total Amount only.

28. “Summary below data” should still be checked.

29. Click OK.

30. Display the subtotals and grand total with the 2 button.

31. Widen column D so you can see the Invoice # and the word Totals.

32. In addition to the subtotals and grand total that are now showing, click the + button at the left of row 44 to display the individual items for only invoice #14915.

33. Click the 1 button to display only the Grand Total.

34. Display all rows with the 3 button.

35. Save the workbook.

TOPIC 3 (REVIEW): Sorting by two or more fields (columns) at once and Ascending versus Descending Sorts

This is a review topic as it was also covered on pages EX271-EX273.

Sorting a list by the values in a single column can be done by selecting any cell in that column whose values you want sorted and clicking the AZ↑ or ZA↓ buttons on the Data tab or in the Sort & Filter button’s menu on the Home tab.

An ascending sort means that labels (text) will be in alphabetical order, numbers will be arranged from smallest to largest, and dates will go from oldest to newest. Descending is the exact opposite. See the table below.

	Ascending	Descending
Text (Labels)	A to Z	Z to A
Numbers	smallest to largest	largest to smallest
Dates	oldest to newest	newest to oldest

However, when you need to sort by the values in multiple columns (perhaps the State and City or maybe the Gender, Last Name, and First Name), you must use the Sort dialog box.

1. In the ReviewAndNewTopics.xlsx workbook, make the Donors sheet active.

2. Select any cell in the block of data, perhaps B4.

3. On the Data tab, in the Sort & Filter group, click the Sort button.

4. Open the first drop-down box in the “Sort by” row and pick State as the most important column by which to sort. “Sort On” should already say Values and “Order” should already be A to Z.

5. Click the “Add Level” button.

6. Open the first drop-down box in the “Then by” row and pick City as the second most important column by which to sort. “Sort On” should already say Values and “Order” should be A to Z.

7. Click the “Add Level” button to add a third row.

8. Open the first drop-down box in the second “Then by” row and pick Last Name as the third most important column by which to sort. “Sort On” should already say Values and “Order” should be A to Z.

9. Click OK.

10. Check that the States are in order first. Then, within each state the Cities are in alphabetical order. Finally, the Last Names are in order within each City.

11. Click the Sort button on the Data tab again. Notice Excel remembers the last multiple column sort and displays all the options as they were last set.

12. Switch the States to a descending (Z to A) sort. Leave the City and Last Name as they were as A to Z (i.e. ascending).

13. Click OK.

14. Click the Sort button on the Data tab again.

15. Switch the City to a descending (Z to A) sort and switch the Last Name to descending so all three columns will be in descending order.

16. Click OK.

17. Save the workbook.

TOPIC 4 (REVIEW): Name by Selection and Paste Names

This is a review topic as it was also covered on pages EX395 and EX397.

When you have a list with a set of column headings or row headings or both, you can use those typed headings as range names for the cells within the body of the list. Each column heading can become the name of the cells directly below that heading; each row heading can become the name of the cells directly to the right of that heading; or both. This saves time as it creates a batch of range names at one time (saving typing) and improves accuracy as Excel creates the names for you.

Remembering that some characters are illegal in a range name, Excel will substitute underscores for spaces and other illegal characters. For example, if a column heading was “First Name”, Excel would create the range name First_Name for the data in that column.

1. In the ReviewAndNewTopics.xlsx workbook, make the Sales sheet active.

2. We do not need to create names for the Invoice Number or Date columns, only the last four columns. So select C3:F15. (If you select only a single cell, Excel will select the entire table automatically.)

3. On the Formulas tab, in the Defined Names group, click the Create from Selection button.

4. We want the labels at the top of each of the 4 columns to be range names, but not the data to the left of the 4 selected columns, so remove the check from Left column.

5. With only Top row checked, click OK.

6. Open the Name Box to see the four new range names. (See the illustration above.)

7. Click on the name Tax. E4:E15 should be selected as those are the cells below the “Tax” column heading.

8. Open the Name Box and select the Amount_of_Sale range. Note in the name the two underscores that replaced the spaces that were in the label.

9. We also need a name for all three columns of numbers together. The easiest way to do that is to create a name yourself by typing it in the Name Box. So select D4:F15, click in the Name Box, and type Total as the name. Press ENTER. (Notice we did not select the labels in row 3 as we did not want them to be part of the named block of cells and we were not using name by selection.)

10. Select J4 and enter the formula =COUNT(Tax) to get a count of the listings. (12)

11. Select J5 and enter the formula =SUM(Tax, Shipping) to get the total of both the Tax and Shipping ranges together. ($476.83)

12. Select J6 and enter the formula =SUM(Total) for the Grand Total. ($6,800.19)

13. Switch to the Cars sheet.

14. Select A3:H11. This time we will use the row labels to create names for the rows of values.

15. Click the Create from Selection button and leave only Left column checked. Click OK.

16. Open the Name Box and make certain the nine new names were created. (There are also the five previous names from the Sales sheet so 14 in all now.)

17. Click the name State to verify that it names B3:H3.

18. Open the Name Box to see what Excel did with the periods in N. Carolina and S. Carolina. Although the spaces were replaced by underscores, the period is a legal character in a name.

19. Select L3:T11.

20. Click the Create from Selection button and leave checks on both Left column as well as Top row. Click OK.

21. Open the Name Box to see how many names the workbook has now. You should have 30.

22. Select the range named Ford to see that the column heading named the cells below it.

23. Select the range named Arizona to see that the row label named the cells to the right of it.

There are now so many names it is difficult to know what cells they refer to. You can make a reference list of the name definitions with the Paste Names command.

24. Select L15 as we want the list to begin there.

25. On the Formulas tab, in the Defined Names group, click the Use in formula button and pick Paste Names at the very bottom of the menu.

26. Click the Paste List button.

27. Scroll down and examine the entire list.

28. Save the workbook.

TOPIC 5 (REVIEW): Print Settings

This is mostly review as all but one topic was also covered on pages EX53 and EX117-EX121.

Among the many print settings for Excel workbooks, there are five that are frequently needed, but often misunderstood. They are:

· Scaling (Page Layout tab’s buttons on page EX53; dialog box on page EX117)

There are two ways to easily control the scaling. Either the button on the File tab’s Print Preview screen or the spinners on the Page Layout tab. (See steps 4 and 7 below.)

· Centering (new topic)

There is a simple checkbox in the Print Setup dialog box that will automatically center the output on the page.

· Titles (page EX117)

Titles are rows and/or columns of labels that you want printed on every sheet as headings.

· Header/Footer (pages EX118-EX120)

Page Headers at the top of every sheet and/or page footers at the bottom of every sheet allow including such information as date of printing, sheet name, file name, etc. They are printed in the margins at the top/bottom of every sheet, although the first page can be different. Formatting like font face, font size, bold, and colors can be applied to any text.

· Defining a different header and/or footer for the first sheet (pages EX118-EX119)

(Orientation, page breaks, margins, and defining the print area are not included here as they seem to be well understood by students.)

1. In the ReviewAndNewTopics.xlsx workbook, make the Employees sheet active.

2. Our manager would like a list printed so that all data fits on the width of the page. It’s all right if multiple sheets of paper are required to print all of the rows. Click the File tab and pick the Print category to display the Print Preview screen.

Scaling Button Page Setup Dialog Box Link Needed Pages and Paging Button

Notice it would require 9 sheets of paper to print all of the data using the current settings.

3. In the column of buttons, click the bottom button. It says “No Scaling” in the illustration above.

4. Pick Fit All Columns on One Page.

5. Reexamine the number of pages that would be required. It probably says 2.

6. Click the Back Arrow button to exit the File tab screen or press the ESC key.

7. Click the Page Layout tab.

On the Page Layout tab, in the Scale to Fit group, the Width is set to “1 page” and the Height is “Automatic.” That is the pair of values for “Fit All Columns on One Page.” Setting those two options on the Page Layout tab is the same as setting “Fit All Columns on One Page” on the Print Preview screen.

8. Our manager’s mind has changed and they want the entire list on a single sheet of paper. Click the File tab and pick the Print category to display the Print Preview screen.

9. Click the bottom button and pick Fit Sheet on One Page. Check the page indicator at the bottom of the screen to be certain it now says 1.

10. Click the Back Arrow button to exit the File tab screen or press the ESC key.

11. Click the Page Layout tab, if necessary.

On the Page Layout tab, in the Scale to Fit group, the Width is set to “1 page” and the Height is also “1 page.” That pair of values means “Fit Sheet on One Page” and setting those two options on the Page Layout tab would accomplish the same outcome as the Fit Sheet on One Page button on the Print Preview screen.

12. Switch to the Schools sheet.

13. Click both 2 buttons to display the schools (but not Undergraduate/Graduate) and the states.

14. Click the File tab and pick the Print category to display the Print Preview screen.

The output is in the upper-left corner of the page. We would like to center the data. Note that there is no button on either the Page Layout tab or the Print Preview screen to set this option. It must be activated in the Page Settings dialog box.

15. Below the last button, click the Page Setup link. (It is probably blue.)

16. In the Page Setup dialog box, click the Margins tab.

17. In the Center on Page section, check the Horizontal checkbox and click OK.

18. Noticing that the preview now shows the data centered horizontally, click the Back Arrow button to exit the File tab screen (or press the ESC key).

On the Page Layout tab, the same Page Setup dialog box can be activated with any one of the three Dialog Box Launcher buttons or even the Print Titles button.

19. Switch to the Employees sheet.

20. On the Page Layout tab, revert back to fitting the columns (but not rows) on one sheet by setting the Height to Automatic. (Width is still “1 page”.)

21. On the File tab, click the Print category to see the preview of the sheets. Notice the bottom button says “Fit All Columns on One Page” again.

Zoom to Page

22. Use the Zoom to Page button in the extreme bottom-right corner of the screen to zoom to 100% size.

23. Examine the top of the sheet where the title “Employees” and the column headings can be seen. Then click the Next Page arrowhead at the bottom of the screen and examine the top of the second sheet. Notice the title and column headings are not showing on that sheet.

Titles can include one or more rows of labels at the top of multiple pages so that copying and pasting column headings is unneeded. We need rows 1-3 on the spreadsheet to be displayed at the top of both printed pages (without copying). Titles can also show one or more columns of labels at the left of multiple sheets, although we do not need that here.

24. Click the Page Setup link to open the Page Settings dialog box.

25. Click the Sheet tab and try to click in the “Rows to repeat at top” textbox. For some reason, Excel will not allow that setting to be changed from the Print Preview screen! Click Cancel.

26. Click the Back Arrow button to exit the File tab screen or press the ESC key.

27. Click the Page Layout tab and click the Page Titles button to open the Page Settings dialog box.

28. On the Sheet tab, click in the Rows to repeat at top textbox.

29. Because we want the first three rows to be repeated on each sheet, select the first three rows by dragging the mouse from the header for row 1 down to the header for row 3. (Be sure you’re on the header, not the cells.) Rows 1-3 will be highlighted and $1:$3 should now show in the Rows to repeat at top textbox.

30. Click the Print Preview button at the bottom of the dialog box to go directly to the Print Preview screen.

31. Check the top of the first sheet to verify that the title and column headings are still there. Then go to page 2 to be certain the top three rows have been duplicated at the top of the second sheet.

32. Click the Back Arrow button to exit the File tab screen or press the ESC key.

In addition to the title and column headings, our manager wants the date the report was printed in the top-right corner of each sheet and the filename in the top-left corner. Lastly, a page number should be included centered at the bottom of each page. Items printed at the top or bottom of each sheet are print headers and print footers.

33. To include headers and footers, click the Page Layout tab and click any one of the three Dialog Box Launcher buttons. (Alternatively, go to the File tab, pick the Print category, and click the Page Setup link.)

34. In the Page Setup dialog box, click the Headers/Footer tab.

35. Click the Custom Header button and make certain the blinking cursor is in the Left section.

36. Click the Insert File Name button to insert the code for the filename. (Do not type the filename yourself as, should you ever change the filename, it would not be updated. The code will always print the current filename even if changed.)

Insert Date Insert File Name

37. Either click in the Right section or press the TAB key twice to move into the Right section of the header.

38. Click the Insert Date button.

39. Click OK to close the Header dialog box and return to the Page Setup dialog box.

40. Placing the page number in the footer is so commonly done, there is a built-in choice for it. Click the drop-down arrow at the right end of the Footer: box to open the list of pre-built footers.

41. Pick Page 1 of ?

The Header/Footer tab of the Page Setup dialog box will display the header and footer in their respective boxes above and below the drop-down Header: and Footer: lists.

42. Click OK.

43. If you are not already on the Print Preview screen, click the File tab and pick the Print category.

44. Check that the left and right headers are at the top of every sheet and that the page number is at the bottom.

Lastly, our manager specified that the first sheet should have a modified header with an additional entry in the center.

45. Click the Page Setup link to open the Page Setup dialog box and click the Headers/Footer tab.

46. In the bottom-left corner of the dialog box, check the Different first page checkbox.

47. Click the Custom Header button.

48. There is now an additional tab, the “First Page Header” tab, that allows specifying a different set of headings for the first sheet. Click the First Page Header tab.

49. With the cursor in the Left section, click the Insert File Name button.

50. Click in the Center section: box (or press TAB) and type your name.

51. Select the characters in your name and click the A (Format Text) button. In the Font dialog box, click on Bold, open the Color: drop-down palette and pick red, and change the Size: to 18. Click OK.

52. Click in the Right section (or press TAB) and click the Insert Date button to place the code for the date of printing.

53. Click OK.

54. Click OK.

55. Check the top of the preview to make certain your name is in the center of the first sheet.

56. Go to page 2 to be certain your name is not at the top of the second sheet.

57. Save the workbook.

TOPIC 6 (NEW): COUNTIF, SUMIF, and AVERAGEIF with a Date Criterion

Although the textbook explains the COUNTIF, SUMIF, and AVERAGEIF functions on pages EX514-EX519, they never explain or show date criteria.

The COUNTIF, SUMIF, and AVERAGEIF functions can take four varieties of criteria. Some require quotation marks and some don’t as outlined in the following table.

Type of Criterion	Description	Examples
A number or the address of a cell containing the value to be matched	No quotes needed	=COUNTIF(D19:F34, 18) =COUNTIF(D19:F34, C12) when C12 contains the value to be matched
Text	Text must be surrounded by quotes. The text is not case sensitive.	=COUNTIF(F9:F63, “rent”) OR =COUNTIF(F9:F63, “Rent”) OR =COUNTIF(F9:F63, “RENT”)
Dates	Dates must be surrounded by quotes.	=COUNTIF(D19:F34, “2/15/18”)
An inequality symbol < <= > >= <> Note: <> means not equal to.	The inequality symbols must be inside quotes no matter the data type that follows. (Thus, the third example in Figure 8-39 in the textbook is incorrect.)	=COUNTIF(H20:K48, “<2/15/18”) =COUNTIF(H20:K48, “>=2/15/18”) =COUNTIF(H20:K48, “<=2/15/18”) =COUNTIF(F9:F63, “<>rent”) =COUNTIF(D19:F34, “>=18”) A number can also be placed inside the quotes along with the inequality symbol.
A combination of a cell address with inequality symbols	The cell address must be outside any quotes (or it will be considered text), but the inequality symbols must be inside quotes, so the part inside quotes and the part outside quotes are joined with an &.	=COUNTIF(A9:D80, “>”&D45) when D45 contains the value to be tested. =COUNTIF(A9:D80, “<=”&F9) when F9 contains the value to be tested. =COUNTIF(A9:D80, “<>”&Y12) when Y12 contains the value you do not want (because <> means not equal to).

Criteria in SUMIF( ) and AVERAGEIF( ) work exactly the same as in COUNTIF( ). However, SUMIF( ) and AVERAGEIF( ) usually need a third argument when the column to be tested is not the same as the column to be summed or averaged. (See examples 6 and 7 below.)

Since we are focusing on date criteria, the third row in the table above illustrates matching an exact date, while the fourth row explains inequalities with dates.

The meanings of the five different inequalities with dates are explained in the table below. (Incidentally, < that resembles a twisted L means less than and > means greater than.)

Meaning	Example
A greater than sign followed by a date means after the date.	>2/6/18
A less than sign followed by a date means before the date.	<2/6/18
When an equal sign is also included with a greater than, it means on or after the date.	>=2/6/18
When an equal sign is also included with a less than it means on or before that date.	<=2/6/18
When a less than and a greater than are combined, it means not equal to .	<>2/6/18

1. In the ReviewAndNewTopics.xlsx workbook, make the Sales sheet active.

2. Select J9 and enter =COUNTIF(B4:B15, “1/2/2018”) to count the number of sales on January 2, 2018. Press ENTER.

3. Select J10 and enter =COUNTIF(B4:B15, “<1/2/2018”) to count the number of sales before January 2, 2018. Press ENTER.

4. Select J11 and enter =COUNTIF(B4:B15, “>1/2/2018”) to count the number of sales after January 2, 2018. Press ENTER.

5. Select J12 and enter =COUNTIF(B4:B15, “>=1/2/2018”) to count the number of sales on or after January 2, 2018. Press ENTER.

6. Select J15 and sum the total of the sales on or after January 1, 2018 by entering =SUMIF(B4:B15, “>=1/1/2018”, E4:E15). Press ENTER.

7. Select J16 and average the sales on or after January 1, 2018 by entering =AVERAGEIF(B4:B15, “>=1/1/2018”, E4:E15). Press ENTER.

8. Save the workbook.

TOPIC 7 (REVIEW): Full Column References in Functions

This is a review topic as this technique was used on pages EX143-EX148.

Some students adopt full column references when calculating any column of values. An example is =SUM(D:D) to sum all of the values in column D. It is simple and doesn’t need dollar signs. However, it does not always work. The problem is it adds every cell in column D even if column D has multiple blocks of values that should only be summed one block at a time. (The textbook used full column references for a column dedicated to ongoing measurements over time.)

1. In the ReviewAndNewTopics.xlsx workbook, make the Full Column References sheet active.

2. Select B3 and enter =SUM(D:D) and press ENTER. 42 is the correct result so thus far things are correct.

3. Select B4 and enter =SUM(E:E) and press ENTER. 8959 is clearly not the correct result.

4. Similarly, create sums for columns F, G, and H in B5:B7. Those are not correct either.

5. Hold down the CTRL key and press the DOWN ARROW key once to jump down to row 40. (Alternatively, scroll to row 40.)

Row 40 is where other numbers have been typed that should not be included in the sums in B3:B7. However, the full column references are doing just that since every value in the column is summed.

If you use full column references, be sure you know they will be correct.

6. Save the workbook.

TOPIC 8 (REVIEW): Nested Functions

This is a review topic as it was also covered on pages EX484-EX488.

Sometimes it is necessary to combine two functions to get a correctly calculated result. For example, if a VLOOKUP( ) function might not find a match, that would cause an error to be displayed in the worksheet. Rather than allow the error to display, you can place the VLOOKUP( ) inside an IFERROR( ) function that can substitute and display a useful message rather than an error.

A common second example is when you need one of three different values displayed in a cell based on testing the data. A single IF( ) function can only produce one of two different values based on its test. However, a nested pair of IF( ) functions can make two tests, one by each IF, and display one of three different values in a cell. (Similarly, three nested IF( ) functions can display one of four different values, etc.)

1. In the ReviewAndNewTopics.xlsx workbook, make the Nested sheet active.

2. Select L5 and type =VLOOKUP(J5, $P$5:$Q$8, 2). Press ENTER. This is not an exact match so the fourth argument is not needed. The dollar signs are needed because we will copy this formula down the column, but the P5:Q8 for the location of the lookup table must not change.

3. Double-click the Fill handle to copy the formula down the column to L23.

4. Noticing the errors that occur when there is no value in the This Year column, edit the formula in L5 to =IFERROR(VLOOKUP(J5, $P$5:$Q$8, 2), "") and copy down to L23.

The last argument in the IFERROR( ) function is “” which is entered by typing two double quotes next to each other. (Hold down SHIFT as you press the quotation mark key to get the character displayed on the top of the key. Then hold down SHIFT as you press the quotation mark key again to type the second double quotation mark.) To Excel “” means display the characters that are typed between the pair of quotes, except there are no characters between the quotes, so display no characters. That creates an empty looking cell rather than the error.

5. Select M5.

We need a formula that will calculate one of three Status values as follows:

· If the amount in the This Year column is greater than the amount in the Prev. Year column, display “Increase”

· If the amount in the This Year column is smaller than the amount in the Prev. Year column, display “Decline”

· If the amount in the This Year column is the same as the amount in the Prev. Year column, display “No change”

6. Enter =IF(J5>I5,"Increase",IF(J5<I5,"Decline","No change")) and press ENTER.

This formula says, if J5>I5 display “Increase.” If not, start another IF( ) function and test for whether J5<I5. If so, display “Decline.” If both of those tests fail, show “No change.”

7. Copy down to M23.

8. Save the workbook.

TOPIC 9 (REVIEW): Naming Objects and Tables

Objects in Excel are items that are superimposed over the worksheet, rather than put into a cell. Examples are charts, pictures, textboxes, Office documents (like a PowerPoint presentation or a Word document), shapes, and SmartArt graphics. All of these objects can be named by deciding on a name and typing it into the Name Box just above the column headers for columns A and B. They can also have Alternate Text, as shown below.

(Tables can also be named in the Table Name: box on the table’s contextual tab on the Ribbon. Other items like PivotTables, slicers, and sparklines are a bit different from objects, but can still be named. Their names are entered in a textbox within their contextual tab on the Ribbon or in their own dialog boxes.)

1. In the ReviewAndNewTopics.xlsx workbook, make the ObjectNames1 sheet active.

2. Click within the table (A5:D8) and, on the Design tab, in the Properties group, click in the Table Name: box and name it ChartTable.

3. Select the textbox (the quote with the red background).

4. Click in the Name Box and change the name of the textbox from TextBox1 to RedQuote.

5. (For Excel 2016 only) Right-click on the textbox and pick Format Shape from the shortcut menu. In the Format Shape panel at the side of the screen, make certain the Shape Options category is selected (is bold). Click the Size & Properties button. Then open the Alt Text section (if necessary) by clicking the tiny triangle at the left of Alt Text.

6. (For Excel 365 only) Right-click the edge of the textbox (not inside) and pick Edit Alt Text from the shortcut menu.

7. In the textbox, type Lubin School Motto.

8. In Excel 2016, close the Format Shape panel with its X button. In Excel 365, close the Alt Text panel with its X button.

9. Select the chart and click in the Name Box. Name the chart StudentEnrollment.

10. Select the graphic (“30 YEARS Executive in Residence”) and name it 30years.

11. Select the lightning bolt graphic and name it Bolt.

12. Switch to the ObjectNames2 sheet.

13. Select the PowerPoint presentation, click in the Name Box, and name it LubinPPT.

14. Select the SmartArt graphic and name it TakeoverCycle.

15. Save and close the workbook.

Assignment #4 Complete steps 1-17 in the “Review Homework Assignment” section below. Save the file with your name and Exercises (e.g. SmithJ Exercises). Submit the file using the provided link on the Assignments page by the due date.

Review Homework Assignment

1. Open the Exercises.xlsx workbook. Save it as your last name and first initial along with the name Exercises (e.g. SmithJ Exercises).

2. On the Products sheet, create an outline. Type your name in F4. Then use the outlining symbols to hide the Vegetables and the Livestock detail columns, but not the Crops and Dairy detail columns. Hide the detail rows so only the subtotals and grand totals show.

3. On the Personnel sheet, in a single sort, order the list by Age in descending order, then by Review Date in ascending order, and then by Salary in ascending order.

4. On the YearlySales sheet, use Name by Selection to make each row label from Athletic Wear to Tennis Racquets a range name for the values in columns B:D.

5. On the YearlySales sheet, starting in J4, use the Paste Names command to paste a list of range names. Then autofit columns J and K.

6. On the ECAP sheet, center the printout horizontally.

7. On the ECAP sheet, create a center print header of your first and last name. Make that header bold, size 14, and purple.

8. On the ECAP sheet, create a print footer of only a centered page number without the word “Page.” (Hint: You must do a custom footer to get just a number without any words.)

9. On the ECAP sheet, create a different centered first page footer of your first and last name.

10. On the Employees sheet, scale to fit the width of the page so the printout is only one sheet wide.

11. On the Personnel sheet, use COUNTIF, SUMIF, and AVERAGEIF to calculate the following. (Do NOT use full column references like B:B.)

a. In P7, how many have a Hired Date before 1/1/2000.

b. In P8, how many have a Review Date on or after 2/1/18.

c. In P12, get the sum of the Salaries for employees with a Hired Date after 12/31/1997.

d. In P13, get the sum of the Salaries for employees with a Review Date on or before 7/1/18.

e. In P17, get the average Age for employees with a Hired Date before 10/1/2004.

f. In P18, get the average Age for employees with a Review Date after 12/31/18.

12. On the Charity sheet, use a VLOOKUP to calculate in column K how many tickets each donor will get for the charity ball by looking into the table in N4:O8. Copy down the column.

13. On the Charity sheet, nest the VLOOKUP inside an IFERROR function so that if an error occurred in column K, that cell will appear blank instead. Copy down the column.

14. On the Charity sheet, in the Status column, use a nested IF function to display the word “Inactive” when the This Year column is empty, display the word “New” when the Prev. Year column is empty, and display “Active” otherwise. (Hint: An empty cell will equal zero.)

15. On the NameIt sheet, name the table with your last name, name the chart with your first name, name the textbox with the two letters that are the initials from your first name and last name, name the star “Star of ECAP”, and name the picture with a name you make up of exactly 10 characters.

16. On the NameIt sheet, give the picture Alt Text of Shadow People <your name>.

17. Save the workbook and submit it to Blackboard using the provided link on the Assignments page by the deadline.

Assignment #5 -- Read the information in the Additional Instructions & Corrections sections for Week 9 to learn how to import a text file into Excel. After you have completed that reading, review the text functions in Appendix A. Go to page EX A23 and complete steps 1-3 of Case Problem 1.

Then complete the steps below in the same workbook (Book Club):

4. Insert a new worksheet and rename it New Member Data.

5. On the New Member Data sheet, import the text file NewMembers.txt starting with row 1. The file is delimited with commas as the delimiter. Place the new data starting in cell A1.

6. Insert a new worksheet and rename it Dropped Member Data.

7. On the Dropped Member Data sheet, import the text file DroppedMembers.txt. The file is delimited with a slash (/) as the delimiter. Start the importing with line 8 of the text file. Place the new data starting in cell A5.

8. On the Members sheet, insert a new column between B and C.

9. In C2, type a function that will properly capitalize each Name in column B.

10. Convert C2:C62 from formulas to constants.

11. Delete column B.

12. Type the heading Name in B1.

13. On the Members sheet, insert a new column between C and D.

14. In D1, type the header Zip Code.

15. In D2, type a formula using the RIGHT( ) function that displays only the 5 digit zip code from the entry in C2.

16. Autofit column D.

17. On the Members sheet, insert a new column between C and D.

18. In D2, type a formula using the LEN( ) function that displays the number of characters in the City/State/Zip in C2.

19. Center D2:D62.

20. In D1 type the heading CSZ Length.

21. Autofit columns D and F.

22. In H2, create a formula using the CONCATENATE( ) function so H2 says “Aurelio Dalton lives in Hot Springs, AK 71901”. Be sure to get the spaces in the appropriate places.

23. If necessary, copy the function in H2 down to H62.

24. Autofit column H and type the heading Concatenation in cell H1.

25. Save the file with your name and Book Club (e.g. SmithJ Book Club).

26. Submit this workbook using the provided link on the Assignments page by the due date.

21