CIS 150 Exam07: Excel Chapter 2 (2016v1)

19. Insert the current date and time in cell A1.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Date & Time button. In the Date & Time menu, you clicked the NOW menu item. Inside the Function Arguments dialog, you clicked the OK button.

5. Use the Create from Selection command to create named ranges for the selected data table in cells B2:E6 using the labels in row 1 as the basis for the names.

In the Formulas Ribbon Tab in the Defined Names Ribbon Group, you clicked the Create from Selection button. Inside the Create from Selection dialog, you clicked the OK button.

11. Hide all of the dependency tracer arrows at once.

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Remove Arrows button.

7. Display the formulas in this worksheet.

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Show Formulas button.

8. Hide the formulas in this worksheet and display the values instead.

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Show Formulas button.

9. Show the tracer arrows from cell C2 to the cells that are dependent on it (cells containing formulas that reference the value or formula in cell C2).

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Trace Dependents button.

10. Show the tracer arrows from the precedent cells to cell C7.

In the Formulas Ribbon Tab in the Formula Auditing Ribbon Group, you clicked the Trace Precedents button.

17. In cell E15, enter a formula to find the highest line item cost this month (cells E2:E14).

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the AutoSum button arrow. In the AutoSum menu, you clicked the Max menu item. You pressed Enter.

18. Insert the current date in cell A1. Do not include the current time.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Date & Time button. In the Date & Time menu, you clicked the TODAY menu item. Inside the Function Arguments dialog, you clicked the OK button.

23. Enter a formula in cell D5 to calculate B5/B4 rounded to 4 decimal places.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the ROUND menu item. Inside the Function Arguments dialog, you typed B5/B4 in the Round Number Formula Input, typed 4 in the Round Num Digits Formula Input, and clicked the OK button.

16. In cell E15, enter a formula to find the lowest line item cost this month (cells E2:E14).

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the AutoSum button arrow. In the AutoSum menu, you clicked the Min menu item. You pressed Enter.

25. Enter a formula in cell D5 to calculate B5/B4 rounded down to 4 decimal places.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the ROUNDDOWN menu item. Inside the Function Arguments dialog, you typed B5/B4 in the Rounddown Number Formula Input, typed 4 in the Rounddown Num Digits Formula Input, and keydowned the Rounddown Num Digits Formula Input collapsible input.

24. Enter a formula in cell D5 to calculate B5/B4 rounded up to 4 decimal places.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the ROUNDUP menu item. Inside the Function Arguments dialog, you typed B5/B4 in the Roundup Number Formula Input, typed 4 in the Roundup Num Digits Formula Input, and clicked the OK button.

27. Enter a formula in cell B1 using the SUMPRODUCT function to calculate the total value of the current leases by multiplying the current monthly rents by the remaining months on each lease. Use the range names Rents and Leases.

In the Formulas Ribbon Tab in the Function Library Ribbon Group, you clicked the Math & Trig button. In the Math & Trig menu, you clicked the SUMPRODUCT menu item. Inside the Function Arguments dialog, you typed Rents in the Sumproduct Array1 Formula Input, typed Leases in the Sumproduct Array2 Formula Input, and clicked the OK button.

12. Enter a formula in cell B7 to calculate the average value of cells B2:B6.

In the Home Ribbon Tab in the Editing Ribbon Group, you clicked the AutoSum button arrow. In the AutoSum menu, you clicked the Average menu item. You pressed Enter.

22. Enter a formula in cell B3 using the VLOOKUP function to find the meaning for the medical abbreviation listed in cell A3. Use the name Abbreviation for the lookup table. The item names are located in column 2 of the lookup table. Be sure to require an exact match.

In the Lookup & Reference menu, you clicked the VLOOKUP menu item. Inside the Function Arguments dialog, you typed A3 in the Vlookup Lookup Value Formula Input, typed 2 in the Vlookup Col Index Num Formula Input, typed Abbreviation in the Vlookup Table Array Formula Input, typed false in the Vlookup Range Lookup Formula Input, and clicked the OK button.

26. Enter a formula in the selected cell using SUMIF to calculate the total expenses for the category Office Expense. Use the range name Category for the Range argument, the text string "Office Expense" for the Criteria argument, and Cost for the Sum_range argument.

In the Math & Trig menu, you clicked the SUMIF menu item. Inside the Function Arguments dialog, you typed Category in the Sumif Range Formula Input, typed Office Expense in the Sumif Criteria Formula Input, typed Cost in the Sumif Sumrange Formula Input, and keydowned the Sumif Sumrange Formula Input collapsible input.

13. In cell E15, enter a formula using a counting function to count the numbers in the Cost column (cells E2:E14).

Inside the Function Arguments dialog, you clicked the Count Value1 Formula Input collapsible input. You clicked cell E2, selected the cell range E2:E14. Inside the Function Arguments dialog, you clicked the Collapsed Mode Input collapsible input. Inside the Function Arguments dialog, you clicked the OK button.

14. In cell D15, enter a formula using a counting function to count the number of cells in the Billable? column (cells D2:D14) that are not blank.

Inside the Function Arguments dialog, you clicked the Counta Value1 Formula Input collapsible input. You clicked cell D2, selected the cell range D2:D14. Inside the Function Arguments dialog, you clicked the Collapsed Mode Input collapsible input. Inside the Function Arguments dialog, you clicked the OK button.

15. In cell D16, enter a formula using a counting function to count the number of blank cells in the Billable? Column (cells D2:D14).

Inside the Function Arguments dialog, you clicked the Countblank Range Formula Input collapsible input. You clicked cell D2, selected the cell range D2:D14. Inside the Function Arguments dialog, you clicked the Collapsed Mode Input collapsible input. Inside the Function Arguments dialog, you keydowned the Countblank Range Formula Input collapsible input.

20. Using cell references, enter a formula in cell B6 to calculate monthly payments for the loan described in this worksheet. Omit the optional arguments. Use a negative value for the Pv argument.

Inside the Function Arguments dialog, you clicked the close dialog button, typed B4/12 in the Pmt Rate Formula Input, typed B4 in the Pmt Nper Formula Input, typed B2/12 in the Pmt Rate Formula Input, typed "-B2" in the Pmt Pv Formula Input, typed B3/12 in the Pmt Rate Formula Input, and clicked the OK button.

6. There is an error in cell B7. Accept Excel's suggestion for fixing the error.

You clicked cell B7, clicked the Smart Tag Button. In the Formula Error menu, you clicked the Update Formula to Include Cells menu item.

3. Edit the formula in cell D2 so the references to cell C2 will update when the formula is copied, and the reference to cell B9 will remain constant. Use AutoFill to copy the formula to cells D3:D6.

You clicked cell D2, pressed the F4 keyboard shortcut, and dragged the Fill Handle tool.

2. Enter a formula in the selected cell to display the owner's draw percentage (cell B6).

You clicked cell D2, typed =B6 in cell D2, and pressed Enter.

1. Enter a formula in the selected cell to calculate the profit projection for 2017: total sales (cell F4) minus the cost of goods sold (cell F5).

You clicked cell F6, typed =F4-F5 in cell F6, and pressed Enter.

4. On the Year1 sheet, in cell B8, enter a formula to display the value of cell B7 from the Salaries sheet.

You selected the cell range B15:D16, clicked cell B13, clicked cell B8, typed = in cell B8, clicked the Salaries tab, clicked the Salaries tab, and clicked cell B7.