Challenge Project 6-10

In this project, you build a workbook by copying sheets from the PerfectRentals-06 data file for this chapter. You nest Logical and Text functions to complete the worksheets.

[Student Learning Outcome 6.2, 6.5]

File Needed: PerfectRentals-06.xlsx (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 6-10.xlsx

Create and save a workbook as [your initials] Excel 6-10. Modify your workbook according to the following guidelines:

• Open the PerfectRentals-06 workbook from your student data files.
• Unhide the two hidden worksheets and copy them into [your initials] Excel 6-10. Close PerfectRentals-06.
• Select the NestSwitch sheet tab and select cell C5.
• Start a SWITCH function. The Expression argument is a nested RIGHT function to extract two characters from the right of cell A5.
• The Value1 argument is “il” and the Result1 argument is Illinois.
• Build four ValueN and ResultN argument pairs for the states in the list.
• Copy the formula to complete the worksheet.
• Click the PhoneNumbers sheet tab and select cell E3.
• Click the Insert command tab and the Symbol button [Symbols group]. On the Symbols tab, verify that the Font choice is (normal text). Scroll the list to find a solid bullet character (•), usually following the alphabetic characters. Select the bullet and note the Character code number in the dialog box; it is 149. Close the Symbol dialog box.
• Start a CONCAT function in cell E3. The Text1 argument is a nested LEFT function to get three characters from cell D3.
• The Text2 argument is a nested REPT function. The Text argument for REPT is a nested CHAR function to display Number 149. The Number_times argument for the REPT function is 10.
• Copy the formula and preserve the bottom border.
• Hide column D.
• Group the two worksheets and insert a header with the file name in the right section.
• Edit document properties to show your name as the author.