Excel problem 7-3. I have attached the file with the data required to complete the problem.
\f1. Open the MortgageCalc-07 workbook from data files folder. 2. Save the workbook as Excel 7-3. 3. Apply the IFERROR function. a. Select H11. b. Use the current PMT function as the value argument. c. Type 0 for the value_if_error argument. d. Apply the Accounting number format. 4. Apply data validation to a range of cells. a. Select cells C6:C12, C14, H6, H9:H10, and H12:H13. b. Click the Data Validation button [Data tab, Data Tools group]. E7-420 c. Select the Settings tab. d. Click the Allow drop-down arrow and choose Decimal. e. Click the Data drop-down arrow and choose greater than or equal to. f. Type 0 in the Minimum box (Figure 7-78). g. Do not click OK. 5. Create an input message for the range of cells that contain validation rules. a. Select the Input Message tab. b. Click in the Title box and type Data Entry as the title of the message. c. Click in the Input message box and type Please type any number greater than or equal to 0. d. Do not click the OK button. 6. Create an error message for the range of cells that contain validation rules. a. Select the Error Alert tab. b. Click the Style drop-down arrow and choose Stop. c. Click in the Title box and type Entry Error! as the title of the error. d. Click in the Error message box and type Incorrect entry! You must type a number greater than or equal to 0. as the message you want to appear when the cell contents are incorrect (Figure 7-79). e. Click OK. 7-78 Validation settings for amount 7-79 Stop style error alert settings 7. Display the Developer tab on the Ribbon if it is not shown. a. Click the Options button [File tab]. b. Click Customize Ribbon in the left pane. c. Click the Developer check box in the Main Tabs group. d. Click OK. 8. Insert a new sheet. a. Insert a new sheet. Select A1:A6. b. Type the following names, pressing Enter after each name: Gary Johnson, Shannon Anderson, Sanjeev Phukan, Lucy O'Riley, Dave Smith, Sandra Kranz. Widen the column if needed. c. Name the new tab Agents. 9. Insert a combo box form control. a. Click the Purchase Information tab. b. Click the Insert Controls button [Developer tab, Controls group]. c. Click the Combo Box (Form Control) button. d. Click and drag to draw a control directly over cell E24. e. Click the Control Properties button [Developer tab, Controls group]. f. Click the Control tab. g. In the Input range box, select A1:A6 on the Agents tab. These values will be selectable in the control. h. Click in the Cell link box and type E24. The choice made in the combo box will be placed in this cell. i. Type 6 as the number of Drop down lines. E7-421 j. Check the 3-D shading box (Figure 7-80). k. Click OK. Click cell E25 to deselect the combo box control. 10. Unlock cells in the worksheet for users to edit. a. Select cells C6:C12, C14, H6, H9:H10, H12:H13, H18, and E22:E24 from the Purchase Information worksheet. b. Right-click one of the selected cells. c. Select Format Cells from the menu. d. Select the Protection tab. e. Clear the Locked check box. f. Click OK. 11. Protect the worksheet with a password. a. Click the Protect Sheet button [Review tab, Changes group]. b. Allow Select unlocked cells only. c. Type the following password: Password1. d. Retype the password: Password1 in the Confirm Password dialog box. Remember that passwords are case sensitive. e. Click OK. When a user attempts to edit a locked cell in the worksheet, a message will appear informing him or her that the worksheet is protected. f. Save the file. If prompted to save the file as a macro-enabled workbook, click No. 12. Save the workbook as a template. a. Click the Save As button [File tab]. b. Type the file name [your initials] Excel 7-3 for the template in the File name box. Since a template uses a different file name extension, you can use the same name as your original workbook for this project. c. Choose Excel Template from the Save as type drop-down box. d. Click Save to place in the default template location. e. Close the template. 7-80 Combo box control defined in Format Control dialog box 13. Create a workbook from a template. a. Open Excel if needed. b. Click the New button [File tab]. c. Click Personal near the top of the gallery. d. Click the icon for your template. A new workbook based on the template is created. If you saved your template elsewhere, double-click the file in its current location. e. Save the workbook as [your initials] Excel 7-3a in your usual location for saving workbooks. 14. Enter data into the workbook as shown in Figure 7-81. 7-81 Excel 7-3 completed worksheet E7-422 15. Protect the workbook structure with a password. a. Click the Protect Workbook button [File tab, Info area]. b. Select Protect Workbook Structure. c. Verify that there is a check mark for Structure. d. Type PassA in the Password box and click OK. e. Retype the password in the Confirm Password dialog box and click OK. 16. Encrypt the workbook with a password. a. Click the Protect Workbook button [File tab, Info area]. b. Select Encrypt with Password. c. Type PassB in the Password box and click OK. d. Retype the password in the Confirm Password dialog box and click OK. 17. Save and close the workbook. Placer Hills Real Estate Mortgage Calculator Current Home Sale and Equity $ $ $ $ $ $ $ - Sale Price Payoff for Home Home Equity Payoff Other Debt to Payoff Other Debt to Payoff Other Debt to Payoff Renovations for new Home $ - Additional Income $ - Amount for down payment New Home Financing $ $ $ $ $ $ - Purchase Price - Down Payment - Loan Amount 0 Loan Term 0.00% Interest Rate #VALUE! Payment without Tax or Insurance - Approximate Taxes - Approximate Insurance - Approx Monthly Tax & Insurance #VALUE! New House Payment Difference between Old & New House Payment Old House Payment New House Payment Difference Created for: Date: Realtor: #VALUE! #VALUE
