CS 285 assignment 3

Preview how the worksheet will look when printed.

HINT: Click File to open Backstage view. Click Print.

On the Expenses worksheet, cell B8 has an error. Display the Smart Tag and accept Excel's suggestion for fixing the error.

HINT: Click cell B8. Click the Smart Tag. Click Update Formula to Include Cells.

Show the precedent arrows for cell B8.

HINT: Click cell B8. On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.

Hide the formulas and display the values instead.

HINT: Click the Back arrow to exit Backstage view. On the Formulas tab in the Formula Auditing group, click the Show Formulas button.

Enter a formula in cell B2 to display the current date and time.

HINT: Click the Formulas tab. In the Function Library group, click the Date & Time button. Select NOW from the list. Click OK in the Function Arguments dialog.

Enter a formula in cell B1 to display only the current date.

HINT: Click the Formulas tab. In the Function Library group, click the Date & Time button. Select TODAY from the list. Click OK in the Function Arguments dialog.

In cell E1, enter a formula using a counting function to count the number of items on sale. Count the numerical values in the ItemID column (cells A7:A20).

HINT: Click the Formulas tab. In the Function Library group, click the More Functions button. Point to Statistical and select COUNT from the list. Type A7:A20 in the Value1 box. Click OK.

In cell E2, enter a formula using a counting function to count the number of items on sale. Count the values in the Item column (cells B7:B20).

HINT: Click the Formulas tab. In the Function Library group, click the More Functions button. Point to Statistical and select COUNTA from the list. Type B7:B20 in the Value1 box. Click OK.

In cell E3, enter a formula using a counting function to count the number of blank cells in the QuantityInStock column (cells E7:E20).

HINT: Click the Formulas tab. In the Function Library group, click the More Functions button. Point to Statistical and select COUNTBLANK from the list. Type E7:E20 in the Value1 box. Click OK.

In the Name Manager, review the information about the ReorderTime name. The name results in a #REF! error. You do not need it in this workbook. Delete the ReorderTime name. Close the Name Manager when you are finished.

HINT: Click the ReorderTime name and then click the Delete button. Click OK. Click Close.

On the Sale Inventory worksheet, in cell E4, enter a formula to calculate the total stock value. Use the SUM function and use the range name StockValue as the function argument.

HINT: Double-click cell E4 to enter Edit mode. Type: =SUM( and then type St to display the Formula AutoComplete list. Double click StockValue to enter it into the formula and then press Enter.

Display the formulas in this worksheet.

HINT: On the Formulas tab in the Formula Auditing group, click the Show Formulas button.

On the Salon Inventory worksheet, cells A4:E20 are selected for you. Use the Create from Selection command to create named ranges for the data table A5:E20 using the labels in row 4 as the basis for the names.

HINT: On the Formulas tab, in the Defined Names group, click the Create from Selection button. The Top row check box is checked by default. Click OK.

Edit the Inventory name so it refers to cells A5:F20 on the Salon Inventory worksheet. Close the Name Manager when you are finished.

HINT: On the Formulas tab, in the Defined Names group, click the Name Manager button. Click in the Refers to box and edit the cell range reference to ='Salon Inventory'!$A$5:$F$20. Click the checkmark to the left of the Refers to box to accept the change. Click the Close button.

There are two Inventory names. Rename the one that is limited in scope to the Sale Inventory worksheet to: InventoryOnSale

HINT: On the Formulas tab, in the Defined Names group, click the Name Manager button. The first Inventory name is selected by default. Click the Edit... button. Type InventoryOnSale in the Name box. Click OK.

On the Formulas tab, in the Formula Auditing group, open the Error Checking dialog and check the rest of the worksheet for errors. Review the information about the next error found and click the button to accept the suggested fix. Continue error checking and accepting suggested fixed until Excel reports that the error check is complete.

HINT: On the Formulas tab, in the Formula Auditing group, click the Error Checking button. Click the Update Formula to Include Cells button. Click the Copy Formula from Left button. Click OK.

Hide all of the tracer arrows at once

HINT: On the Formulas tab, in the Formula Auditing group, click the Remove Arrows button.

On the Loan2 worksheet, show the dependent arrows for cell B6.

HINT: On the Formulas tab, in the Formula Auditing group, click the Trace Dependents button.

Enter a formula in cell B7 using the PMT function to calculate the monthly loan payment. Use cell references as the function arguments. Remember, payments will be monthly, so divide the annual interest rate by 12. Use a negative number for the Pv argument. Do not include the optional arguments.

HINT: On the Formulas tab, in the Function Library group, click the Financial button, and click PMT. Enter B4/12 in the Rate argument box. Enter B5 in the Nper argument box. Enter -B3 in the Pv argument box. Click OK.

Using the Insert Function dialog, enter a SUM function in cell E7 to calculate the total of cells B7:D7.

HINT: On the Formulas tab, in the Function Library group, click the Insert Function button. In the Insert Function dialog, expand the Or select a category list, and select Most Recently Used. Click SUM. Click OK in the Function Arguments dialog.

Enter a formula in cell F5 using the logical function IF to display do not order if the quantity in stock (cell D5) is greater than the reorder level (cell E5) and order if it is not.

HINT: On the Formulas tab, in the Function Library group, click the Logical button. Select IF. In the Logical_test box, enter D5>E5. In the Value_if_true box, type do not order. In the Value_if_false box, type order. Click OK.

Enter a formula in cell B2 using the VLOOKUP function to find the item name for the item number listed in cell B1. Use the named range Inventory for the lookup table. The item names are located in column 2 of the lookup table. Require an exact match.

HINT: On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select VLOOKUP. Type B1 in the Lookup_value argument box. Type Inventory in the Table_array argument box. Type 2 in the Col_num argument box. Type False in the Range_lookup box. Click OK.

Use the command on the Ribbon to open the Function Arguments dialog to enter a SUM function in cell B1 to calculate the total of cells B12:D12. Do not open the Insert Function dialog first. Be sure to use a cell range and not individual cell references.

HINT: On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUM. Type B12:D12 in the Number1 box. Click OK in the Function Arguments dialog.

Enter a formula in cell B2 to calculate the average value of cells B12:D12. Be sure to use a cell range and not individual cell references.

HINT: On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and click Average. Type B12:D12 in the Number1 box. Click OK in the Function Arguments dialog.

In cell B3, enter a formula to find the highest (maximum) monthly income total (cells B12:D12). Be sure to use a cell range and not individual cell references.

HINT: On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select MAX. Type B12:D12 in the Number1 box. Click OK in the Function Arguments dialog.

In cell B4, enter a formula to find the lowest (minimum) monthly income total (cells B12:D12). Be sure to use a cell range and not individual cell references.

HINT: On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select MIN. Type B12:D12 in the Number1 box. Click OK in the Function Arguments dialog.

Enter a formula in cell B3 to combine the text from cells B2 and C2 to display the customer name in the format Elizabeth Ramey. Don't forget the argument for the space.

HINT: On the Formulas tab, in the Function Library group, click the Text button. Select CONCAT from the list. Type B2 in the Text 1 box. Type ″ ″ in the Text 2 box. Type C2 in the Text 3 box. Click OK.

Enter a formula in cell H3 to display the text from cell H2 so all the letters display in lowercase.

HINT: On the Formulas tab, in the Function Library group, click the Text button. Select LOWER from the list. Type H2 in the Text box, and click OK.

Enter a formula in cell D3 to display the text from cell D2 so the first letter in each word is capitalized.

HINT: On the Formulas tab, in the Function Library group, click the Text button. Select PROPER from the list. Type D2 in the Text box, and click OK.

In cell B4, enter a formula using TEXTJOIN to combine the text from cells A2:C2 to display the customer name in the format Ms. Elizabeth Ramey. Use a space as the delimiter. Ignore blank cells. Use a cell range as the Text1 argument.

HINT: On the Formulas tab, in the Function Library group, click the Text button. Select TEXTJOIN from the list. Type " " in the Delimiter argument box. Type TRUE in the Ignore_empty argument box. Type A2:C2 in the Text 1 box. Click OK.

Enter a formula in cell F3 to display the text from cell F2 so all the letters display in uppercase.

HINT: On the Formulas tab, in the Function Library group, click the Text button. Select UPPER from the list. Type F2 in the Text box, and click OK.

On the Summary sheet, in cell B3, enter a formula to display the value of cell E8 from the Expenses worksheet.

HINT: Type = and then click the Expenses sheet tab. Click cell E8. Press Enter.

On the Summary sheet, in cell B4, enter a formula to calculate the value of cell B1 from the Income worksheet minus cell E8 from the Expenses worksheet.

HINT: Type = and then click the Income sheet tab. Click cell B1. Type -. Click the Expenses sheet tab. Click cell E8. Press Enter.

Use Formula AutoComplete to enter a SUM function in cell E12 to calculate the total of cells B12:D12. Be sure to use a cell range and not individual cell references.

HINT: Type = in cell E12 to begin the formula. Type SU to shorten the Formula AutoComplete list. Double-click SUM to enter it in your formula. Enter B12:D12. Press ENTER to complete the formula.

On the Sale Inventory worksheet, cells F7:F20 are selected. Name them as follows: StockValue

HINT: Type StockValue in the Name box to the left of the formula bar. Press Enter.