MGSC 290 Exam 2 … computer part – Flashcards
Unlock all answers in this set
Unlock answersquestion
1) Switch the rows and columns in the chart, so the data point
answer
On the Chart Tools Design tab, in the Data group, click the Switch Row/Column button.
question
2) Enter a formula using the REPLACE function to replace only the fourth character in cell A2 with the single character "-" (the hyphen character).
answer
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 4. In the Num_characters argument box, enter 1. In the New_text argument box, enter "-". Click OK.
question
3) Add a linear trendline to this chart. Use the default trendline formatting.
answer
Click the Chart Elements button that appears near the upper right corner of the chart. Click the Trendline check box to add a checkmark.
question
4) Add a report filter for the Employee field and use it to filter the PivotTable to show only data where the value of the Employee field is Dr. Patella.
answer
Click the Employee field in the PivotTable Fields pane and drag it to the Filters box in the bottom part of the pane Click the filter arrow next to the report filter. Click Dr. Patella. Click OK.
question
5) Create and apply a new conditional formatting rule. Apply the default icon set Three traffic lights (unrimmed) icon set, but show only the icon, not the cell value. Change the values so the green circle icon (the first icon) will be applied if the cell value is >=90 percent and the yellow circle icon (the second icon) will be applied if the cell value is <90 and >=10 percent.
answer
On the Home tab, in the Styles group, click the Conditional Formatting button. Click New Rule. Expand the Format Style list and select Icon Sets. Click the Show Icon Only check box. Type 90 in the first value box. Type 10 in the second value box. Click OK.
question
6) Use Goal Seek to find the value for cell D12 that will result in a value of $650,000 for cell B15. Accept the solution.
answer
On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Goal Seek... Enter 650000 in the To value box. Enter D12 in the By changing cell box. Click OK. Click OK.
question
7) Use Consolidate to enter values in the selected cells by summing data from cells B3:D8 in the worksheets Bob, Cathy, and Mario. Include links to the source data to display subtotals.
answer
On the Data tab, in the Data Tools group, click the Consolidate button. Click in the Reference box and then click the Bob worksheet tab. Click and drag to select B3:D8. Click the Add button. Click the Cathy worksheet tab. Click Add. Click the Mario worksheet tab. Click Add. Click the Create links to source data check box. Click OK.
question
8) Enter a nested IF function in cell A10 to calculate the owner's draw amount. If the value in cell B7 is greater than 400000, then the draw amount is 20000. Use a nested IF formula in the Value_if_false argument to return a draw amount of 10000 if the value in cell B7 is greater than 300000 and 0 if it is not
answer
On the Formulas tab, in the Function Library group, click the Logical button and select IF. In the Logical_test box, type B7>400000. In the Value_if_true argument box, type 20000. 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 B7>300000. In the Value_if_true argument box, type 10000. In the Value_if_false argument box, type 0. Click OK.
question
9) In cell F9, enter a formula using the INDEX function to return the ending loan balance for the row listed in cell F8. Use the named range Schedule to reference the cell range for the schedule of loan payments. The Ending Balance column is column 6.
answer
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter Schedule. In the Row_num box, enter F8. In the Column_num box, enter 6. Click OK.
question
10) In cell F8, enter a formula using the MATCH function to return the row number of the payment date listed in cell C8. Use the named range PayDates to reference the cell range for the Payment Date column. Require an exact match.
answer
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 C8. In the Lookup_array box, enter PayDates. In the Match_Type argument box, enter 0. Click OK.
