Excel Assessment #3
When using the goal seeking function, how many input variables are there
3
CONCATENATE
=CONCATENATE([text1, text2, text3,.....])
FIND
=FIND(find_text, within_text, [start_num])
HLOOKUP
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
INDEX
=INDEX(array, row_num, [col_num])
LOOKUP
=LOOKUP(lookup_value, lookup_vector, result_vector)
RIGHT
=RIGHT(text, [num_chairs])
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
After you have identified the criteria that prevent a solution from being feasible, you have two choices: You can do nothing and declare that there is no solution to the problem, or you can ____ to create a feasible solution.
Adjust the constraints
The first row (key values) of the lookup table must be sorted in ____ order to use an HLOOKUP function with a TRUE lookup type.
Ascending
A common way of storing data so that it is usable in other programs is to save it in a ____ file.
Comma-Delimited
The ____ Wizard separates the values in a text string into columns or fields.
Convert text-to-columns
Forecast
Creates a copy of data and inserts it on a new sheet alone w the forecast formulas and predict the future values
_________ provides additional information about a data point Data label Data points Legend Data series
Data Label
Filters
Data Tab Clear, reapply, advanced
When creating a data table based on a formula in a single cell, it is generally easiest to use which of the following Goal seeking What if analysis Data Table Solver
Date Table
T/F If a lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP opens up the Lookup Wizard.
False
T/F In a one-dimensional table, Excel searches one dimension, such as the columns, and then searches another dimension, such as the rows, to find the value at the intersection of a single row and column.
False
T/F Placing a field in the Row Labels area of a PivotTable displays data from that field in columns and rows.
False
T/F Solver can produce four different reports: summary, answer, sensitivity, and limits.
False
T/F The CONCATENATE function combines the values in a range of cells into one numeric item in a new cell.
False
T/F When you use a lookup type of TRUE, the VLOOKUP function looks only for an exact match of the lookup value.
False
T/F With Solver, you can change the values of up to 500 cells at one time to reach a goal in the objective cell.
False
T/F The LAST function returns the last character or characters in a text string, based on the number of characters specified.
False The RIGHT function returns the last character or characters in a text string, based on the number of characters specified.
T/F A(n) PivotSheet report is an interactive report that lets you summarize and analyze a data set.
False A(n) PivotTable report is an interactive report that lets you summarize and analyze a data set.
The PivotTable ____ task pane contains a list of fields in the data source you selected for the PivotTable report.
Fields
The first goal when working with unstructured data is to determine the ____ you need so you can find the best way to change the unstructured data into structured data.
Format
To open the format chart areas task pane, right click on the chart border and select _______
Format chart area
To open the format chart area task pane, right click on the chart border and select Chart properties Formatting properties Chart formatting dialogue box format chart areas
Format chart areas
A PivotChart report represents source data as a ____.
Graphic
Cell Styles
Home Tab 1) Good, bad, neutral 2) Data and model 3) Titles and headings 4) Themed Cell Styles 5) Number format
Sort Filter
Home Tab 1) Sort A-Z 2) Sort Z-A 3) Custom Sort
You must answer four questions before using optimization to solve a problem. They do NOT include ____.
How much does each decision variable cost?
A(n) ____ results when Solver cannot determine the combination of decision variables that satisfies all of the constraints.
Infeasible Solution
Charts
Insert Tab
Tables
Insert Tab
To change the number of times Solver attempts to solve a problem, click Options in the Solver Parameters dialog box, change the value in the ____ box, and then click the OK button.
Iretations
Unlike a regular chart, you can change the layout and data displayed in a PivotChart report by ____.
Moving Fields
Analyze
Pivot Table Fields 1) List 2) Quantity 3) Cost 4) Total Filters, Columns, Rows, Values
The ____ function returns the last character or characters in a text string, based on the number of characters specified.
RIGHT
Which of the following inputs are not required for Goal Seeking Set Cell To Value By Changing Select Array
Select Array
Which of the following is not an option under the what if analysis drop down goal seeking Solver Data Table Scenario Manager
Solver
LOOKUP v VLOOKUP
The LOOKUP function allows a user to search for a piece of data in a row or column and return a corresponding piece of data in another row or column. The VLOOKUP function is similar but only allows a user to search vertically in a row and only returns data in a left-to-right procedure.
OR
The OR function returns TRUE if any of the conditions are TRUE. The OR function returns FALSE if all conditions are FALSE.
You use VLOOKUP when ____ of the lookup table contains the data you are looking up.
The first column
T/F A PivotChart report represents source data as a(n) graphic.
True
T/F A doughnut chart serves a similar purpose to a pie chart, except that it is able to show more than one set of data
True
T/F Dividing text into columns is also referred to as parsing text into columns.
True
T/F Excel provides unconditional formatting tools, which are tools you can use to identify a set of conditions and specify the formatting if those conditions are met
True
T/F If all of the constraints, which represent the availability of resources, have slack values, the solution isn't the optimal solution that maximizes the available resources.
True
T/F Sparklines are used show trends in a series of values, such as seasonal increases or decreases
True
T/F The INDEX function allows you to retrieve data from multidimensional tables.
True
T/F The Subtotal command creates summary reports that quickly organize data into categories with subtotal calculations.
True
T/F The TRIM function removes all spaces in a text string except for the single spaces between words.
True
T/F The cells in a worksheet usually display combinations of text or numbers; the math that generates the answers is hidden.
True
T/F When solving problems with nonlinear objective functions, you must enter a variety of starting points to ensure that the answer that Solver reports is actually the optimal solution.
True
T/F You can often use a LOOKUP function instead of a VLOOKUP or HLOOKUP function with a TRUE lookup type.
True
The Query Wizard lets you choose your data source and select the database table and fields you want to import into a workbook.
True
The easiest way to create a PivotChart report is to use an existing PivotTable report as the source data.
True
If Solver attempts the maximum number of iterations without the objective cell converging to an answer, the assumption is that the solution is ____.
Unbounded
You can change the calculation used in the VALUES area by right-clicking a value in the VALUES area to open the shortcut menu, and then clicking Field Settings to open the ____ dialog box.
Value Field Settings
The default field setting in the ____ area is to calculate a sum of the items in this drop area by the categories in the Row Labels area or Column Labels area.
Values
IF
performs a logical test and return one value for a TRUE result, and another for a FALSE result
AND
returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE
Purpose of FIND function
returns the location of a sub-string in a string
NOT
returns the reversed logical value If the logical_value is TRUE, then the NOT function will return FALSE. If the logical_value is FALSE, then the NOT function will return TRUE.
In an INDEX function, the argument ____ refers to the number of the row in the range referenced in the first argument.
row_num
