Group 6
On the expenses worksheet, cell B8 has an error, display the Smart Tag and accept Excel's suggestion for fixing the error On the formula 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 the error check is complete
Click Cell B8 Click the Smart Tag Click update formula to include cells Click the formulas tab Click the error checking button Click the update formula to include cells button Click the copy formula from left button
Enter a formula in cell B1 to display only the current date Enter a formula in cell B2 to display the current date and time
Click the formulas tab Click the Date & Time button Click today Click the Date & time button Click NOW
In cell 1 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) 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)
Click the formulas tab Click the More functions button Click count Type A7:A20 in the value 1 box and press enter to submit your answer Click ok Click the More function button Click COUNTA TYPE B7:B20 and press enter Click the more functions button Click Countblank Type E7:E20
There are two inventory names. Rename the one that is limited in scope to the Sale Inventory worksheet to: inventoryOnSale 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 finished
Click the formulas tab Click the Name Manager button in the Defined Names group The first inventory name is selected by default. Click the edit button Type InventoryOnSale in the Name box Click ok Click the reordertime name Click the delete button Click ok
On the Loan2 worksheet, show the depended arrows for cell B6 Select cell B8 and show the precedent arrows Hide all of the trader arrows at once
Click the formulas tab Click the Trace Dependents button Click cell B8 Click the Trace Prexendants button Click the remove arrows button in the formulas tab
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 option arguments
Click the formulas tab Click the financial button, in the function library group Select PMT Type B4/12 in the rate argument box Type B5 in the Nper argument box Enter -B3 in the Pv argument box
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 is it is not
Click the formulas tab Click the logical button in the Function Library group 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
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 name inventory for the lookup table. The item names are located in column 2 of the lookup table. Require and exact match
Click the formulas tab Click the lookup & reference button Select VLOOKUP TYPE B1 in the Lookuo_value argument box Type inventory in the table_areay argument box Type 2 in the Col_num argument box Type false in the range_lookup box
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 Using the insert function dialog, enter a Sum function in cell E7 to calculate the total of cells B7:D7
Click the formulas tab Click the math & trig button Click SUM Type B12:D12 in the number 1 box and press enter Click ok in the function arguments dialog Click the insert function button Expand the Or select a category list Select most recently used Click ok
In cell B3 enter a formula to find the highest (max or min) monthly income total cells B12:D12
Click the formulas tab Click the more functions button Click MAX Type B12:D12 in the number 1 box and press enter to submit your answer Click the more function button again Click MIN Type B12:D12 in the number 1 box and press enter to submit your answer
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
Click the formulas tab Click the more functions button Click average Type B12:D12 in the number 1 box and press enter to submit your answer
Edit the inventory name so it refers to cells A5:F20 on the SalonInventory worksheet. Close the Name Manager when you are finished
Click the formulas tab Click the name manager button Edit the cell range reference to ='Salon Inventory'!$A$5:$F$20 in the refers to box. Press enter Click the check mark to the left of the refers to box Click the close button
Display the formulas in this worksheet Preview how the worksheet will look when printed Hide the formulas and display the values instead
Click the formulas tab Click the show formulas button Click the file tab Click print Click the back arrow to exit backstage view Click the show formulas button
Enter a formula in cell B3: to combine the text from cells B2 and C2 to display the customer name in the format Maxwell Arch
Click the formulas tab Click the text button Select CONCAT Type B2 and press enter Type " " in the text 2 box press enter Type C2 in the text box 3 press enter
In cell B4 enter a formula using TEXTJOIN to combine the text from cells A2:C2 to display the customer name in the format Mr. Maxwell Arch. Use a space as the delimiter ignore blank cells. Use a cell range as the text 1 argument
Click the formulas tab Click the text button Select TEXTJOIN type " " in the Delimter box and press enter Type TRUE in the ignore_empty box Type A2:C2
Enter a formula in cell D3 to display the text from cell D2 do the first letter in each word is capitalized
Click the formulas tab Click the text button in the function library group Select proper Type D2 in the text box and press enter Click the text button Select upper Type F2 and press enter Click the text button Select lower Type H2 and press enter
On the Saleinventory 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
Double click cell E4 Type =SUM(St to display the formula AutoComplete list Double click StockValue to enter it into the formula Press enter
Use formula Autocomplete to enter a sum function in cell E12 to calculate the total cells of B12:D12. Be sure to use a cell range and not individual cell references
Type =SUM in E12 Double click SUM Type B12:D12 and press enter to complete the formula
On the saleinventory worksheet, cells F7:F20 are selected. Name them StockValue On the SaleInventory 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
Type StockValue in the Name box to the left of the formula bar. Press enter Click the formulas tab Click the create from selection button
On the summary sheet, in cell B3, enter a formula to display the value of cell E8 from the expenses worksheet On the summary sheet, in cell B4, enter a formula to calculate the value of cell B1 from the income worksheet no use cell E8 from the expenses worksheet
Type an equal sign in cell B3 Click the expenses worksheet tab Click cell E8 Press enter Type and equal sign in cells B4 Click the income worksheet tab Click cell B1 Type - Click the expenses sheet tab Click cell E8 Press enter
