Chapter 6,7,12
IF you use the function, =MATCH(66, A1:A4,0), on the table below, what would be the result? A B C D 1 79 83 85 25 2 99 19 35 28 3 94 58 47 49 4 66 29 26 18
4
Which is the Array argument in =INDEX(A2:B5,MATCH(MAX(B2:B5),B2:B5,0),1)?
A2:B5
A list of active and inactive Add-ins is in the
Add-ins dialog box
When using advanced filtering, leaving a cell empty in the criteria range will return which of the following?
All records
________ specify the restrictions or limitations imposed on a spreadsheet model as Solver finds an optimum value.
Constraints
You need to know how many clients you have in a dataset with these fields in these ranges. Criteria are in A363:F365. ClientID A4:A357 ClientName B4:B357 OrderDate C4:C357 Item. D4:D357 Quantity E4:E357 Price F4:F357
DCOUNT(A6:F357,A3,A363:F365)
Which database function returns the lowest value from the dataset based on your criteria?
DMIN
What is the purpose of an Excel What-if scenario?
Detailed set of values that represent different possible situations
Which of the following is not a solving method supported by Solver?
Duplex
Why would you put a negative sign in many financial functions?
Excel requires a negative interest rate.
Which of the following is not an argument in a Database function?
Filter
What do Miles and Rate represent in the following line of code?
Function arguments.
What color are comments in VBA?
Green
What is < > equivalent to in Advanced Filters?
Not equal to
What selections in the File tab do you make to load an add-in?
Options and then Add-ins
What function returns the total present value of an investment with a fixed rate, specified number of payment periods and a series of identical future payments?
PV
Which of these is not a capability of the Advanced Filter tool
Provide pop up filters
Solver does not support which of the following calculations on the target cell?
Range
What line of code do you to add at the beginning of a procedure if your worksheet (Sheet 2) is protected and your procedure alters the locked cell H4, if your password is ExcelIsFun
Sheet 2.Unprotect Password:= "ExcelIsFun"
Which is the best what-if analysis tool to determine the optimal combination of products to produce in order to maximize profit?
Solver
Which of the following is false about a VBA object?
Starts with '
What best describes a criteria range
The group of adjacent cells that specifies the conditions used to control the results of a filter
What type of match would the following function return when the lookup array is sorted in descending order?
The match would be the smallest value in the list without going over the value of A3.
What is the purpose of the Row_num argument in the following INDEX function? =INDEX(A2:B5,MATCH(MAX(B2:B5),B2:B5,0),1)
To return the highest value in the range B2:B5
Why would you nest MATCH in an INDEX function?
To use the value returned in the Match function as a lookup in the index function
Which of the following is not an option in the Trust Center?
Trusted Websites
What does the following line of code do to the selected cell
Turns off bold formatting in the selected cell.
A(n) ________ is a rule that Solver must enforce to reach the objective value.
binding constraint
The ________ are the cells containing variables whose values change within the constraints until the objective cell reaches its optimum value
changing variable cells
The limit of no more than a $50,000 down payment is an example of a(n) ________ when calculating the optimal combination of down payment and interest rate to purchase a new printing press.
constraint
The biggest difference between Scenario Manager and Solver is that Solver uses
constraints
A(n) ________ specifies the cell that contains a formula that produces the value to be optimized with Solver.
objective cell
The advanced filter tool can ________.
search for unique records, only
