Exam 2 Flashcards
What two indicators demonstrate that a worksheet is grouped?
Having the word in the title bar and it being in editing mode
On the move or copy dialog box, how can a worksheet be moved/copied?
Use the to book section to add the workbook you want to move to, then choose where you want to place it
How can flexible 3-D references be added?
By using a wildcard
If both the source and destination workbooks are open is data automatically replicated in the destination workbook?
Yes
How do you create a hyperlink?
Select the text/object want to use as the hyperlink, then click insert tab and hyperlink
Where can named ranges be made and where can they then be seen?
Created in the named ranges from labels workbook using create from selection command, and can view list of named ranges by opening the name manager dialog box in formulas on ribbon.
Are named ranges absolute cell references and once defined do all with equivalent names then get replaced?
Yes
How can additional information be added to a hyperlink?
Add a ScreenTip
Do you right click a cell with a hyperlink if do not want to activate it?
Yes
Can named ranges have dashes and where by default does excel store workbook templates?
No. User’s Custom Office Templates folder.
How does split command happen?
Window’s group of the view tab. This opens panes.
Where are records and fields found?
In the rows and the columns.
What should you do after each data range?
Provide a data definition table and a short description of each field.
How are duplicates removed?
Click any cell in the range, on data tab in data tools group then click remove duplicates button and choose which field of duplicates to remove
What does doing sorting in ascending order mean?
Alphabetically from A to Z, numbers from smallest to largest, and dates from oldest to newest
Can you sort with a custom list?
Yes
Does outline tool for subtotal enable to hide some data?
Yes
How do you filter from multiple fields?
Select the filter button from other column headers in the data range.
Does the advanced filter combine fields using AND and OR logical operators?
Yes
State 3 features of excel tables that are not supported by ranges?
Built in sorting and filtering tools, table styles, and total rows.
Is a calculated field one that contains a formula that references other fields and have enclosed brackets with names in them?
Yes
What does @ stand for in structural field?
Current record or row in the table
Will VLOOKUP function automatically include additional rows as more records are added to lookup table?
Yes
What does the 1 in =SUBTOTAL(1, Customers_Table(ID]) stand for?
The number of the function to use
What would be the formula to calculate the income field from employees table?
=AVERAGE(Employees[Income])
What would the VLOOKUP for 3rd field in Sales_Result that exactly matches the LOOKUP value in cell B10 look like?
=VLOOKUP(B10, Sales_Result, 3, False)
Would =COUNTIF(Media[SITE], B4) be the syntax for the number of cells containing the value “Facebook” in the Media table?
Yes
What is the syntax for HLOOKUP?
=HLOOKUP(lookup_value, table_array, row_index, index_num[range_lokkup]).
How are character patterns created for partial lookups?
By the use of wildcards
What is the syntax for the match function?
MATCH(lookup_value, lookup_array, [match_type=1]).
What is IF function syntax?
=IF(logical_test1, value_if_true1, logical_test2, value_if_true2, …)
When do AND and OR functions come out as true?
When all of the conditions are true and when one of the conditions are true respectively.
What is syntax for both?
…(logical1, [logical2], [logical3], …)
What is the only difference between COUNTIF and SUMIF functions?
SUMIF has sum_range as an extra in it’s syntax.
What are the syntax for COUNTIFS and SUMIFS?
COUNTIFS(range1, criteria1, [range2], [criteria2], …). SUMIFS(sum_range, range1, criteria1, [range2], [criteria2], …)
How does excel display missing combinations within the PivotTable?
Blank cells
Is category related with the axis and series related with legend of PivotTable?
Yes
How do you drill down a PivotTable?
Double click on any cell within the table, then excel opens sheet telling you how that value was created
Does PivotTable arrange categories in a row or column alphabetically for text fields and by date for date fields?
Yes
What is the difference between how one and two variable data tables perform what-if analyses?
One table: one input cell and several result cells. Two table: two input cells and one output cell.
Does two variable data table contain a reference to the result cell at the intersection of first row and column?
Yes
What is another name for cost volume-profit analysis?
Break-even analysis
Where is the range of possible values for the input cell entered?
First column or row and in the subsequent
What does scenario manager do?
Creates many scenarios that are used to complete what-if analyses
What two places are where result cells are found?
Scenario summary and scenario pivot table reports
How can scenario manager be opened?
Click on a cell that could change, go to the data tab, what-if analysis, and scenario manager
What must be identified in order to create a report through a pivot table?
Result cells, values of input cells, and current worksheet values
Where can objective cells be used within excel?
In the solver parameters dialog box
Does every solver need an objective and one or more variable cells?
Yes
How many types of constraints does a solver support?
6
Are overall highpoints seen as global optimum and local optimum similar? Are these decisions based off of previous results?
Yes. Yes.
Does solver support the iterative models Simplex LP method, GRG Nonlinear method, and Evolutionary method?
Yes
What are the three reports solver generates and their definitions?
Answer report which summarises the results of a successful solution. Sensitivity and limits which is usually used to investigate mathematical aspects and check reliability.
Is a binding constraint included in solver model and a limiting factor in getting to solution?
Yes
