CIS 150 PRACTICE 07: EXCEL CHAPTER 2

11 September 2022
4.7 (114 reviews)
27 test answers

Unlock all answers in this set

Unlock answers (23)
question
Enter a formula in the selected cell to calculate the value of cell E9 times 3.
answer
Type =E9*3. Press Enter.
question
On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet.
answer
Type = and then click the ByMonth sheet tab. Click cell B3. Press Enter.
question
Edit the formula in cell B9 so the references to cell E2 will update when the formula is copied, and the reference to cell B8 will remain constant. Use AutoFill to copy the formula to cells B10:B12.
answer
Double-click cell B9 to edit the formula. Change the formula to be =E2*$B$8). Press Enter. Click the Fill Handle tool and drag down to cell B12. Release the mouse button.
question
Enter a formula in the selected cell to display the value of cell E3.
answer
Type =E3. Press Enter.
question
Use the Create from Selection command to create named ranges for the data table B8:E11 using the labels in row 1 as the basis for the names.
answer
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.
question
There is an error in cell D6. Accept Excel's suggestion for fixing the error.
answer
Click cell D6. Click the Smart Tag. Click Copy Formula from Above.
question
Display the formulas in this worksheet.
answer
On the Formulas tab in the Formula Auditing group, click the Show Formulas button.
question
Hide the formulas in this worksheet and display the values instead.
answer
On the Formulas tab in the Formula Auditing group, click the Show Formulas button to hide the formulas.
question
Show the tracer arrows from cell B5 to the cell(s) that are dependent on it (cells containing formulas that reference the value or formula in cell B5).
answer
On the Formulas tab, in the Formula Auditing group, click the Trace Dependents button.
question
Show the tracer arrows from the precedent cells to cell B5.
answer
On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.
question
Hide all of the dependency tracer arrows at once.
answer
On the Formulas tab, in the Formula Auditing group, click the Remove Arrows button.
question
Enter a formula in cell E4 to calculate the average value of cells B4:D4.
answer
On the Home tab, in the Editing group, click the AutoSum button arrow and select Average. Press Enter.
question
In cell F12, enter a formula using a counting function to count numbers in the Ordered column (cells F2:F11).
answer
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNT from the list. Click and drag to select cells F2:F11. Click OK.
question
In cell C12, enter a formula using a counting function to count the number of items in the Item column (cells C2:C11).
answer
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNTA from the list. Click and drag to select cells C2:C11. Click OK.
question
In cell G12, enter a formula using a counting function to count the number of blank cells in the Received column (cells G2:G11).
answer
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNTBLANK from the list. Click and drag to select cells G2:G11. Click OK.
question
In cell H12, enter a formula to find the lowest percentage of items received in the order (cells H2:H11).
answer
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Min. Press Enter.
question
In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11).
answer
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Max. Press Enter.
question
Insert the current date in the selected cell. Do not include the current time.
answer
On the Formulas tab, in the Function Library group, click the Date & Time button. Click TODAY. Click OK.
question
Insert the current date and time in the selected cell.
answer
On the Formulas tab, in the Function Library group, click the Date & Time button. Click NOW. Click OK.
question
Using cell references, enter a formula in cell B7 to calculate monthly payments for the loan described in this worksheet. Use a negative value for the Pv argument.
answer
On the Formulas tab, in the Function Library group, click the Financial button, and click PMT. Enter B5/12 in the Rate argument box. Enter B6 in the Nper argument box. Enter -B4 in the Pv argument box. Click OK.
question
Enter a formula in cell C2 to return a value of yes if the value in cell E8 is greater than or equal to the value in B2 or no if it is not.
answer
On the Formulas tab, in the Function Library group, click the Logical button. Select IF. Enter E8>=B2 in the Logical_test box. Enter yes in the Value_if_ true box, and no in the Value_if_false box. Click OK.
question
Enter a formula in cell B2 using the VLOOKUP function to find the total sales for the date in cell B1. Use the name DailySales for the lookup table. The total sales are located in column 5 of the lookup table. Be sure to require an exact match.
answer
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 DailySales in the Table_array argument box. Type 5 in the Col_num argument box. Type False in the Range_lookup box. Click OK.
question
Enter a formula in cell D2 to calculate C2/C14 rounded to 3 decimal places.
answer
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click ROUND. In the Number argument box, type C2/C14. In the Num_digits argument box, type 3. Click OK.
question
Enter a formula in cell D2 to calculate C2/C14 rounded up to 3 decimal places.
answer
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click ROUNDUP. In the Number argument box, type C2/C14. In the Num_digits argument box, type 3. Click OK.
question
Enter a formula in cell D2 to calculate C2/C14 rounded down to 3 decimal places.
answer
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click ROUNDDOWN. In the Number argument box, type C2/C14. In the Num_digits argument box, type 3. Click OK.
question
Enter a formula in cell E1 using SUMIF to calculate the total quantity in stock for items from the company "ColorFab". Use the range name Company for the Range argument, the text string "ColorFab" for the Criteria argument, and InStock for the Sum_range argument.
answer
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIF. In the Range argument box, type Company. In the Criteria box, type "ColorFab". In the Sum_range argument box, type InStock. Click OK.
question
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 SpecialPrice and MembershipsSold.
answer
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMPRODUCT. In the Array1 argument box, type SpecialPrice. In the Array2 argument box, type MembershipsSold. Click OK.