Deck 10: Formula Auditing, Data Validation, and Complex Problem Solving

Question 1

Which of the following complex problem solving methods, features, or options allows you to provide Excel with all of the known rules of your problem as well as the goal you are seeking, and then attempts as many different solutions to the problem as possible?

A) Trial and error method
B) Solver feature
C) Goal Seek feature
D) Enable iterative calculations option

Answer 1

B

Question 2

The Trace Error button is located in the ____ dialog box.

A) Error Checking
B) Calculation Checking
C) Formula Checking
D) none of the above

Answer 2

A

Question 3

The ____ button allows you to move through a formula step by step.

A) Check Formula
B) Evaluate Formula
C) Review Formula
D) Verify Formula

Answer 3

B

Question 4

A(n) ____ Report shows the answer that Solver found, which constraints were in place, and which values in the worksheet Solver manipulated in order to solve the problem within those constraints.

A) Solution
B) Resolution
C) Answer
D) Calculation

Question 5

Formula ____ allows you to examine formulas to determine which cells are referenced by those formulas.

A) calculating
B) auditing
C) formatting
D) trending

Question 6

One of the tools that Excel provides to solve complex problems is ____, which allows you to specify up to 200 cells that can be adjusted to find a solution to a problem.

A) Goal Seek
B) Trendlines
C) Solver
D) Data Solutions

Question 7

Formula auditing tools include a tracer ____, which is drawn from a cell that appears in a formula in another cell or from a cell that contains a formula with cell references.

A) arrow
B) triangle
C) link
D) none of the above

Question 8

Which of the following is the path to the Watch Window button?

A) (FORMULAS tab | Formula Auditing group)
B) (FORMULAS tab | Calculations group)
C) (FORMULAS tab | Observations group)
D) (FORMULAS tab | Watch group)

Question 9

Identifying ____ highlights where changes will occur in the worksheet as a result of changing the value in the cell you are identifying as a referenced cell.

A) precedents
B) dependents
C) examples
D) relationships

Question 10

Which of the following is NOT a type of data validation criteria you can assign to a cell?

A) any specified value
B) whole numbers
C) dates
D) Bold style formatting

Question 11

The ____ error code indicates that a cell reference in a formula is not valid.

A) #DIV/0!
B) #NAME?
C) #VALUE!
D) #REF!

Question 12

Which of the following is the path to the Trace Precedents button?

A) (FORMULAS tab | Formula Auditing group)
B) (FORMULAS tab | Trace group)
C) (HOME tab | Formulas group)
D) (HOME tab | Auditing group)

Question 13

Which of the following is the path to the Data Validation button?

A) (FORMULAS tab | Validation group)
B) (DATA tab | Data Tools group)
C) (DATA tab | Validation group)
D) (FORMULAS tab | Data Tools group)

Question 14

Which of the following is the path to the 'Remove All Arrows' arrow?

A) (FORMULAS tab | Reviewing group)
B) (FORMULAS tab | Formula Auditing group)
C) (FORMULAS tab | Arrows group)
D) None of the above

Question 15

When a cell contains a(n) ____ reference, it means that one of its defining values is itself.

A) formatted
B) circular
C) error
D) connecting

Question 16

The ____ allows you to monitor the values and formulas in cells as you work on a worksheet.

A) Show Window
B) Data Window
C) Watch Window
D) Calculation Window

Question 17

A cell containing a formula that references other cells is said to have ____.

A) dependents
B) calculations
C) precedents
D) references

Question 18

Which of the following is the path to the Error Checking button?

A) (AUDITING tab | Formulas group)
B) (FORMULAS tab | Error Checking group)
C) (FORMULAS tab | Formula Auditing group)
D) (ERROR CHECKING tab | Watch Cells group)

Question 19

The ____ Window button opens a separate window that displays values and formulas for specific cells that you choose to monitor.

A) Formula
B) Watch
C) Error
D) Monitoring

Question 20

A cell whose value relies on another referenced cell is said to be a(n) ____ of that referenced cell.

A) precedent
B) partner
C) example
D) dependent

Question 21

To check for invalid data entered as the result of a formula or automated tools such as Goal Seek, use the '____' command.

A) Bold Invalid Data
B) Delete Invalid Data
C) Circle Invalid Data
D) Replace Invalid Data

Question 22

____ is a feature of Excel that allows you to restrict access to workbooks.

A) Workbook Protection Management (WPM)
B) Information Rights Management (IRM)
C) Shared Workbooks Management (SWM)
D) Digital Rights Management (DRM)

Question 23

Which of the following is the path to the Solver button?

A) (FORMULAS tab | Data Tools group)
B) (DATA tab | Analysis group)
C) (ANALYSIS tab | Data group)
D) None of the above

Question 24

The Goal Seek command is located on the ____ menu.

A) What-If Analysis
B) Problem Solving Tools
C) Calculation Settings
D) none of the above

Question 25

Which of the following is not a primary use of the Scenario Manager?

A) Find a scenario-style solution to a complex problem by changing a single variable
B) Create different scenarios with multiple sets of changing cells
C) Build summary worksheets that contain the different scenarios
D) View the results of each scenario on a worksheet

Question 26

Which of the following is the path to the 'What-If Analysis' button?

A) (DATA tab | Calculation Tools group)
B) (DATA tab | Formula Tools group)
C) (DATA tab | Data Tools group)
D) (DATA tab | Problem Solving Tools group)

Question 27

Which of the following is not a valid consideration when creating scenarios?

A) Exclude cells that contain sheet title, row heading, or column heading text
B) Exclude cells that contain formulas
C) Exclude cells that remain constant across scenarios
D) Exclude cells that are input cells for your model

Question 28

The ____ should be used before sharing a workbook publicly.

A) Scenario Manager
B) Document Inspector
C) Information Manager
D) Workbook Inspector

Question 29

____ are named combinations of values that are assigned to variables in a model.

A) Constraints
B) Tables
C) Scenarios
D) Data validation rules

Question 30

The Scenario Manager command is located on the ____ menu.

A) Data Tools
B) Formula Auditing
C) Scenarios
D) What-If Analysis

Question 31

For each model, it is helpful to define a ____ scenario that represents the starting assumptions.

A) baseline
B) topline
C) bottomline
D) sideline

Question 32

As shown in the accompanying figure, Solver allows you to set a constraint that includes a cell reference followed by a(n) ____.

A) value
B) operator
C) format
D) none of the above

Question 33

You can prepare a workbook for sharing by clicking the ____ tab in Backstage view.

A) Save & Send
B) Options
C) Recent
D) Info

Question 34

An Answer Report shows original and final values of the target cell and changing cells plus ____.

A) cell formatting
B) constraints
C) previous trial and error solutions
D) all scenarios

Question 35

The ____ worksheet generated by the Scenario Manager actually is a worksheet in outline format.

A) Calculation Summary
B) Model Summary
C) Scenario Summary
D) Solution Summary

Question 36

The Show Iteration Results check box is located in the ____ dialog box.

A) Goal Seek solution
B) Data Validation Rules
C) Add Scenarios
D) Solver Parameters

Question 37

Which of the following is NOT an issue that must be kept in mind when using Solver?

A) The constraints may be constructed so that an answer that satisfies all of them cannot be found
B) Multiple answers might solve the problem
C) Some problems do not have solutions
D) Constraint precision is incompatible with most forms of automatic scaling

Question 38

An Answer Report's ____ column indicates binding constraints.

A) Constraints
B) Cell Value
C) Status
D) Formula

Question 39

The ____ feature is especially useful when working with very large worksheets.

A) model
B) summary
C) outline
D) auditing

Question 40

Which of the following commonly used Solver parameters sets the number of possible answer value combinations that Solver should try?

A) Max Time
B) Iterations
C) Constraint Precision
D) Use Automatic Scaling

Question 41

Which of the following are types of error styles?

A) Stop
B) Arrow
C) Warning
D) Information

Question 42

Goal seeking allows you to manipulate multiple cells to find an optimal solution to a complex problem.

Question 43

The error code #DIV/0! indicates that there are no common cells in two ranges a formula identifies as having an intersection.

Question 44

After you display precedent arrows, you must remove them level by level.

Question 45

Excel data validation rules apply when you enter data in a cell manually and when a cell's value is calculated by a formula.

Question 46

You can attempt to solve a complex worksheet problem manually, also known as the trial and error method.

Question 47

Which of the following actions can you perform using data validation rules?
a.
recalculate all formulas
b.
place restrictions on values
c.
provide a message to the user
d.
display an error message when a restricted, or invalid, value is entered

Question 48

The goal for the Solver feature is to maximize the solution within the constraints set.

Question 49

Excel's data validation tool is a what-if analysis tool that allows you to record and save different sets of what-if assumptions used to forecast the outcome of a worksheet model.

Question 50

Each cell referenced in a formula is a dependent of the cell containing the formula.

Question 51

Errors can be introduced into a worksheet in formulas.

Question 52

Which of the following are error codes?

A) #N/A!
B) #VALUE!
C) #NULL!
D) #OVR/0!

Question 53

Excel uses the Calculate Formulas button to move through a formula step by step.

Question 54

When using the Custom data validation type, you can use a formula that evaluates to either true or false.

Question 55

To ensure that values in specific cells contain only nonnegative whole numbers, Scenario Manager rules should be applied to those cells to restrict a user from entering invalid data.

Question 56

Entries that are the result of calculations will not produce data validation errors.

Question 57

Errors can be less obvious, introduced through formulas which, while technically correct, introduce unintended results to a worksheet.

Question 58

Which of the following are methods or tools you can use to solve complex worksheet problems?

A) trial and error
B) Goal Seek
C) Solver
D) data validation

Question 59

Obvious formula errors in a worksheet are indicated by a(n) ____ in the cell that contains the formula.

A) error code, such as #/DIV0!
B) series of pound signs (#####)
C) green triangle
D) blue square

Question 60

Auditing the formulas in a worksheet can give you insight into how a worksheet is structured.

Question 61

The only way to switch between scenarios is to use the Scenario Manager.

Question 62

The Document Inspector dialog box allows you to choose which kinds of content you want to inspect. _________________________

Question 63

When considering an approach to a complex problem in Excel, start with the least complex method of attempting to solve the problem. _________________________

Question 64

A Solver constraint that is binding is one that limits the final solution in some way.

Question 65

Before you use IRM, your computer must first be configured with IRM, as should the computers or mobile devices of anyone attempting to use a document that includes IRM features.

Question 66

PivotTables summarize large amounts of data, and can be rearranged and regrouped to show the data in various forms.

Question 67

You should name ranges for all input variables before creating your first scenario.

Question 68

_________________________ rules can be set up to display prompts and error messages when users select a cell or enter invalid data.

Question 69

If you save the 2013 version of a workbook using the Excel 97-2003 workbook file format without changing the file name, you will overwrite the 2013 version of the workbook.

Question 70

Red ________________________ indicate that one of the referenced cells contains an error.

Question 71

_________________________ will try many possible solutions to find one that solves the problem subject to the constraints placed on the data.

Question 72

When trying to solve some problems, you can make a(n) educated guess if you are familiar with the data and the structure of the workbook. _________________________

Question 73

The Simplex LP solving method uses geometric progression to solve problems.

Question 74

You can use scenario auditing to review the formulas in a worksheet. _________________________

Question 75

A(n) precedent is a limitation on the possible values that a cell can contain. _________________________

Question 76

Solver creates a Summary Solution Report on the same worksheet as the data after it finds a solution.

Question 77

Data _________________________ allows you to set cells so that the values they accept are restricted in terms of type and range of data.

Question 78

A constraint that is not binding is one that is not a limiting factor in the solution that Goal Seek provides.

Question 79

If you show a scenario and change values on the scenario worksheet, it is the same as changing the scenario.

Question 80

You can use _________________________ to manage two sets of Solver data.