CISM Practice Exam 2
Use Consolidate to enter values in the selected cells by summing data from cells B3:D8
in the worksheets Uptown, Downtown, and City Center. Do not include links to the
source data. - On the Data t
...
CISM Practice Exam 2
Use Consolidate to enter values in the selected cells by summing data from cells B3:D8
in the worksheets Uptown, Downtown, and City Center. Do not include links to the
source data. - On the Data tab, in the Data Tools group, click the Consolidate button.
Click in the Reference box and then click the Uptown worksheet tab. Click and drag to
select B3:D8. Click the Add button. Click the Downtown worksheet tab. Click Add. Click
the City Center worksheet tab. Click Add. Click OK.
Add a hyperlink from the selected cell to link to cell A1 in the Inventory List worksheet. -
On the Insert tab, in the Links group, click the Hyperlink button. Under Link to: click
Place in this document. In the Or select a place in this document box, click Inventory
List. Click OK.
Insert a screenshot of the open Internet Explorer browser window showing employment
statistics into this worksheet. Include a hyperlink with the image, but do not have Excel
remember your choice. - On the Insert tab, click the Illustrations button to expand the
Illustrations group. Click the Screenshot button. Click the first thumbnail in the gallery.
Click Yes.
Group together worksheets Q1-Q2 and Q3-Q4 so you can make changes to both at the
same time. - Hold the Shift key and click the sheet tab labeled Q3-Q4.
Remove the hyperlink from the selected cell. - Right-click and select Remove Hyperlink.
Use Consolidate to create subtotals using the Sum function for values in the named
range ItemsOrdered. Include both the top row and left column labels. The named range
ItemsOrdered has been defined for you. The consolidation should show the subtotals
only and not the details. - On the Data tab, in the Data Tools group, click the
Consolidate button. Type ItemsOrdered in the Reference box. Click the Top row check
box. Click the Left column check box. Click OK.
Ungroup the worksheets. - Click the Estimates worksheet tab.
Enter a formula using DAVERAGE to calculate the average value in the Total Spent
column for rows that meet the criteria in the criteria range A1:G2. The database is
defined by the named range CustomersDB. - On the Formulas tab, in the Function
Library group, click the Insert Function button to open the Insert Function dialog.
Expand the Or select a category list and select Database. Select DAVERAGE, and click
OK. Enter CustomersDB in the Database argument box. In the Field argument box,
enter "Total Spent". In the Criteria argument box, enter A1:G2. Click OK.
In cell D6, enter a formula using AND to display TRUE if the daily sales (cell C6) is
greater than the overall average (cell C3) and the daily sales (cell C6) is greater than
the employee's average (cell C4). Use cell references and enter the arguments exactlyas described in this question. - On the Formulas tab, in the Function Library group, click
the Logical button. Click AND. In the Logical1 argument box, enter C6>C3. In the
Logical2 argument box, enter C6>C4. Click OK.
In cell D6, enter a formula using OR to display TRUE if the daily sales (cell C6) is
greater than the overall average (cell C3) or the daily sales (cell C6) is greater than the
employee's average (cell C4). Use cell references and enter the arguments exactly as
described in this question. - On the Formulas tab, in the Function Library group, click
the Logical button. Click OR. In the Logical1 argument box, enter C6>C3. In the
Logical2 argument box, enter C6>C4. Click OK.
Enter a nested IF function in cell B8 to calculate the bonus amount. If the value in cell
B6 is greater than 1500, then the bonus amount is 2500. Use a nested IF formula in the
Value_if_false argument to return a draw amount of 500 if the value in cell B6 is greater
than 1000 and 50 if it is not. - On the Formulas tab, in the Function Library group, click
the Logical button and select IF. In the Logical_test box, type B6>1500. In the
Value_if_true argument box, type 2500. Click in the Value_if_false argument box, and
then click the arrow in the Name box to the left of the formula bar and select IF. In the
Logical_test box, type B6>1000. In the Value_if_true argument box, type 500. In the
Value_if_false argument box, type 50. Click OK.
Edit the AVERAGEIFS formula in cell E2 using IFERROR so if the formula results in an
error, the message not in PO will display instead of the #DIV/0! error. Edit the formula
directly in the cell or the formula bar. Remember to use quotation marks around the text
string. - Edit the formula in the formula bar or the cell to:
=IFERROR(AVERAGEIFS(Prices,POs,D1,Companies,"DermoMagic"),"not in PO")
In cell B3, enter a formula using INDEX to look up the expected delivery date for the
purchase order at the row position in cell B2. Use the named range JunePOs as the
Array argument. The price is found in the fifth column of the array. - On the Formulas
tab, in the Function Library group, click the Lookup & Reference button. Select INDEX.
Click OK to select the array,row_num,column_num option. In the Function Arguments
dialog, in the Array box enter JunePOs. In the Row_num box, enter B2. In the
Column_num box, enter 5. Click OK.
In the selected cell, enter a formula using MATCH to look up the row position of the item
listed in cell B1 in the array named POitems. Require an exact match. - On the
Formulas tab, in the Function Library group, click the Lookup & Reference button.
Select MATCH. In the Function Arguments dialog, in the Lookup_value box, enter B1. In
the Lookup_array box, enter POitems. In the Match_Type argument box, enter 0. Click
OK.
In cell B10, enter a formula using PV to calculate the value today (the present value) of
the four-year tuition plan. Use cell references wherever possible. The annual interest
rate for your investment account is stored in cell B8, the number of monthly payments in
cell B7, and the monthly payment amount in cell B6. Payments will be made at thebeginning of every period. Pay attention to the time periods for the interest rate and
payment schedule. Remember to express the Pmt argument as a negative. - On the
Formulas tab, in the Function Library group, click the Financial button. Click PV. Enter
B8/12 in the Rate box. Enter B7 in the Nper box. Enter -B6 in the Pmt box. Type 1 in the
Type box. Click OK.
In cell B6, enter a formula to calculate the future value of this savings strategy. Use cell
references wherever possible. The annual interest rate is stored in cell B5, the number
of payments in cell B4, and the monthly payment amount in cell B3. Remember to
divide the annual interest rate by 12 and use a negative value for the Pmt argument. -
On the Formulas tab, in the Function Library group, click the Financial button. Click FV.
Enter B5/12 in the Rate argument box. Enter B4 in the Nper argument box. Enter -B3 in
the Pmt argument box. Click OK.
In cell B14, enter a formula using NPV to calculate the value today (the present value)
of the tuition payment option 3. Use cell B7 as the Rate argument and the cell range
B10:B13 as the Value1 argument. Use cell references for all values. - On the Formulas
tab, in the Function Library group, click the Financial button. Click NPV. Enter B7 in the
Rate box. Enter B10:B13 in the Value1 box. Click OK.
In the selected cell, use the CONCATENATE function to combine the values of cells B3
and C3 with a space in between. The result of the formula should look like this:
Alexandria VA - On the Formulas tab, in the Function Library group, click the Text
button. Select CONCATENATE from the list. Type B3 in the Text1 box. Type " " in the
Text2 box. Type C3 in the Text3 box. Click OK.
Enter a formula using the EXACT function to display TRUE if the value of cell D2
matches the value of cell E2 exactly. - On the Formulas tab, in the Function Library
group, click the Text button. Click EXACT. In the Text1 argument box, enter D2. In the
Text2 argument box, enter E2. Click OK.
Enter a formula using the REPLACE function to replace only the first three character in
cell A2 with the two characters "HH". Be sure to use 1 as the Start_num argument. - On
the Formulas tab, in the Function Library group, click the Text button. Click REPLACE.
In the Old_text argument box, enter A2. In the Start_num argument box, enter 1. In the
Num_characters argument box, enter 3. In the New_text argument box, enter "HH".
Click OK.
Enter a formula in cell E2 using SUMIFS to calculate the total price (use the named
range JunePrices) where the value in the JunePOs named range is equal to the value in
cell D1 and the value in the JuneCompanies named range is equal to "Salon Supplies".
- On the Formulas tab, in the Function Library group, click
[Show More]
