CIS 150 PRACTICE 07: EXCEL CHAPTER 2
Display the formulas in this worksheet.
On the Formulas tab in the Formula Auditing group, click the Show Formulas button.
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.
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.
Hide all of the dependency tracer arrows at once.
On the Formulas tab, in the Formula Auditing group, click the Remove Arrows button.
Show the tracer arrows from the precedent cells to cell B5.
On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.
Insert the current date and time in the selected cell.
On the Formulas tab, in the Function Library group, click the Date & Time button. Click NOW. Click OK.
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.
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.
In cell F12, enter a formula using a counting function to count numbers in the Ordered column (cells F2:F11).
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.
Enter a formula in the selected cell to display the value of cell E3.
Type =E3. Press Enter.
There is an error in cell D6. Accept Excel's suggestion for fixing the error.
Click cell D6. Click the Smart Tag. Click Copy Formula from Above.
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.
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.
Hide the formulas in this worksheet and display the values instead.
On the Formulas tab in the Formula Auditing group, click the Show Formulas button to hide the formulas.
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).
On the Formulas tab, in the Formula Auditing group, click the Trace Dependents button.
In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11).
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Max. Press Enter.
In cell H12, enter a formula to find the lowest percentage of items received in the order (cells H2:H11).
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Min. Press Enter.
Insert the current date in the selected cell. Do not include the current time.
On the Formulas tab, in the Function Library group, click the Date & Time button. Click TODAY. Click OK.
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.
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.
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.
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.
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.
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.
Enter a formula in cell D2 to calculate C2/C14 rounded to 3 decimal places.
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.
Enter a formula in cell D2 to calculate C2/C14 rounded down to 3 decimal places.
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.
Enter a formula in cell D2 to calculate C2/C14 rounded up to 3 decimal places.
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.
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.
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.
In cell C12, enter a formula using a counting function to count the number of items in the Item column (cells C2:C11).
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.
In cell G12, enter a formula using a counting function to count the number of blank cells in the Received column (cells G2:G11).
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.
Enter a formula in cell E4 to calculate the average value of cells B4:D4.
On the Home tab, in the Editing group, click the AutoSum button arrow and select Average. Press Enter.
On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet.
Type = and then click the ByMonth sheet tab. Click cell B3. Press Enter.
Enter a formula in the selected cell to calculate the value of cell E9 times 3.
Type =E9*3. Press Enter.
