MIS 112 Final
The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________.
"horizontally, vertically"
Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned?
#N/A
What does Excel return when your MATCH() function uses an exact match option and searches for an item that is not part of the lookup_array or lookup_vector?
#N/A
Which of the following error codes indicates that a formula refers to a cell that is invalid? This type of error can occur if a referenced cell is deleted from the worksheet.
#REF!
What value should be given to the 'match_type' argument in the MATCH function if you want the MATCH function to find the first value exactly equal to lookup_value?
0
Please refer to the attached workbook. How many Savings type accounts were opened in Central and West side branches (combined)?
134
Please see the attached workbook. The formula =LOOKUP("Mary", A2:A5, C2:C5) returns what value?
28
In Excel, the DATE function takes _____ arguments.
3
Please see the attached workbook. The scaling factor used is different from the default. Use Print Preview to see its effect. How much is the scaling factor?
400%
The Excel CODE function returns the character code for its argument. What does the following formula return? =CODE("B")
66
Which of the following is the correct formula for converting the hexadecimal argument "3BC" to its decimal equivalent?
=HEX2DEC("3BC")
To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to define the location you want to jump to in the document. Which of the following is the right option to create a hyperlink to the bookmark 'QrtlyProfits' in the document 'Annual Report.doc' located at http://example.microsoft.com. The link text to be displayed is "Quarterly Profit Report".
=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly Profit Report")
Please see the attached workbook. Which formula is used to provide a link to cell A4 in the same workbook with link text same as text present in A4?
=HYPERLINK("[hyperlink_examples.xlsx]A4",A4)
Create a new workbook and enter a date in A1 that is NOT a weekend. Which of the following formulas will generate a series of weekdays only (no Saturdays or Sundays)?
=IF(WEEKDAY(A1)=6,A1+3,A1+1)
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the entry in cell A1 is an odd number?
=ISODD(A1)
Which Excel function returns the serial number of current date and time?
=NOW()
To get rid of ALL blank spaces in a string (e.g., in "Jo hn Doe "), which Excel function will you use?
=SUBSTITUTE()
Which of the following formulas is used to determine the date of the Sunday that was most recent?
=TODAY()-MOD(TODAY()-1,7)
Which of the following is the correct output of the formula '=TRIM(" A B C D ")'?
A B C D
What is one of the DRAWBACKS of using a pivot table?
A Pivot Table does not update automatically when information changes in source data
When using the formula "=WORKDAY(DATE(2014,1,4),10)", what does the output show?
A date that is ten working days from (after) January 4, 2014
A pivot chart is which of the following?
A graphical representation of a data summarized in a pivot table
Which of the following statements is FALSE?
A pivot chart is always based on a pivot table, i.e., changes to the pivot chart must be made by changing the underlying table
A Loan amortization schedule in Excel is :
A table of values that shows various types of information for each payment period of a loan
Please refer to the attached workbook. The ______ , ________ and ________fields are in the Column, Rows and Values section respectively.
AcctType, branch, count of amount
An Excel formula may return which of the following errors?
All of the above
Which of the following is TRUE about Pivot tables?
All of the answers provided are TRUE
Which of the following file types can Excel import?
All of the file types
Data validation on a cell or range can do which of the following?
All of the options provided are valid
Which of the following options will return Year2016?
All the options are correct
How will you return a cell formatted as a date or time, back to its default or original format?
Both a) and b)
How can you access the dialog box for Pivot Table options?
Both b and c
How are some icons hidden in Icon Set conditional formatting?
By choosing "No Cell Icon" in the Edit the Rule Description box.
Please see the attached workbook. What will be the grade of Adams if the following formula is used in the cell C2? =LOOKUP(B2,{45,50,60,70,80,90},{"F","E","D","C","B","A"})
C
Please see the attached workbook, Sheet2. Which of the following do Theme effects operate on?
Charts, Shapes
What command (within Conditional Formatting) is used to delete all the rules relevant to a particular group of cells?
Clear Rules
How do you set up a data validation (using the data validation dialog box) that restricts data entry to a time less than a specified time?
Click the Settings tab (in the Data Validation box). In the Allow box, select 'Time'. In the Data box, select 'less than' and enter the end time.
_____________ interest refers to an interest calculation where interest is credited to the investment balance (and the investment then further earns interest on the interest credited).
Compound
From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data validation criteria. Many options are available from the Allow drop-down list. Which of the following options would you select if you want the user to enter a date such that it falls on a Sunday?
Custom
When you have imported data from an external data source, you can change the properties of the 'external data range' by using the External Data Properties dialog box. How will you open this dialog box?
Data (tab) -> Connections (group) -> Properties
What command (within Conditional Formatting, Manage Rules) is used to delete a specific rule from a worksheet?
Delete Rule
Which older version of Excel file can Excel 2016 NO LONGER open?
Excel can open all of these files.
True or false: Excel can only import HTML files from your local computer.
False, Excel can import HTML files from the local computer or the web.
Assuming you have a report that contains multiple products from XYZ company. What Pivot table feature would you use to see the data of any one product?
Filter
Which of the following is NOT considered a style attribute?
Formula
Please use the attached workbook as an external data source. The workbook has a table called 'Employee'. When you import data, after selecting all the columns from the table using the query wizard (Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value greater than 21 for 'Age' column.
From the Column to Filter list, select Age. In the right panel, select 'is greater than' from the first drop-down list and then select '21.0' from the second drop-down list
Please use the attached workbook as an external data source. The workbook has a table called 'Employee'. When you import data, after selecting all the columns from the table using the query wizard (Data(tab) -> Get External Data (group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value lesser than 30000 for 'Salary' column.
From the Column to Filter list, select Salary. In the right panel, select 'is less than' from the first drop-down list and then select '30000.0' from the second drop-down list
Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?
HYPERLINK()
Which function returns the contents of a cell at the intersection of a specified row and column from a range?
INDEX()
The basic formula for computing simple interest is:
Interest = Principal * Rate * Term
Which of the following best explains the MATCH function?
It returns the relative position of an item in an array that matches a specified value in a specified order
Which of the following is the MOST compelling motivation for using HLOOKUP()?
It usually reduces duplicate data storage
The __________ function can be used to find the count of characters in a word or text string.
LEN()
What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE?
Link_location is required, Friendly_name is optional
What is Not the purpose of using Slicers?
Make table more beautiful
How do you remove a filter in the PivotTable Field List?
Move the cursor over the filter icon next to the field name you want to remove the filter from. Click the arrow that appears, and then choose the Clear Filter From option.
Please see the attached workbook. What value of Discount is returned when you use an VLOOKUP function with a quantity entered (i.e., value looked up) of 1001 and a range-lookup parameter of FALSE?
None
Which function is used to calculate depreciation in Excel using the straight line method?
None of the above
Which of the following is FALSE about Pivot Table Slicers?
None of the answers provided is false
Please refer to the attached workbook. What happens if the following value filter is added to the existing report? Value filter: Sum of Sales does not equal 5000.
Nothing happens. Both the records remain.
Please refer to the attached workbook. What happens if the following value filter is added to the existing report? Value filter: Sum of Sales is greater than 5000.
Only one record corresponding to the 'CA' state remains as its total sales amount is greater than 5000.
What is the correct syntax for the PMT function, if we are only considering the required parameters/arguments for the function? (Note: "nper" means Number of Payments)
PMT(rate,nper,pv)
Which of the following options would you choose if you want to remove all manual page breaks in the worksheet?
Page Layout (tab) -> Page Setup (group) -> Breaks -> Reset All Page Breaks
By default, Excel prints worksheets in ___________ orientation
Portrait
Which function can be used to make crude/simple horizontal separators between cells?
REPT()
The ________ function is one solution to account for for floating point number errors. It also serves to round off values to the desired number of decimal places.
ROUND()
Which of the following is NOT a summary function for Pivot tables?
Regression
______ locates a substring in another text string and returns the start position of the substring.
SEARCH()
The function ______________ updates specific characters in a string with a new value (i.e., with new text). Hint: This function is used when you know the text to be replaced but not the specific location.
SUBSTITUTE()
In the Share tab of Backstage View, which of the options is available?
Send using E-mail
To create a single file web page, what file format does a user have to select from the Save as Type drop-down list?
Single File Web Page
Please see the attached workbook. Row 5 is hidden and contains a word in cell A5. What is the result of using the spell checker when the word is hidden?
Spell checker will be able to check the spelling of the word because the contents of hidden rows and columns are checked.
In Excel 2016, in order to change the date's default format, one needs to change ___________ settings.
System-wide (i.e., all of Windows)
Which of the following options is TRUE with respect to the function PROPER() in Excel?
The PROPER() function doesn't handle names with an embedded capital letter, such as McDonald.
Which of the following statements is TRUE with respect to the VLOOKUP() function?
The VLOOKUP() function always looks up a value in the first column of the lookup range.
If a pivot chart is created and the underlying table is deleted, what occurs?
The pivot chart remains.
When a range is formatted using a 2-color scale, what happens?
The range will contain a gradient between the two specified colors.
There are four boxes/areas below the PivotTable Field List. Which of the following is NOT one of these four boxes?
Tools
Which is NOT a feasible option to prevent cell contents from being printed?
Use the custom number format that consists of two colons.
Please see the attached workbook. Which LOOKUP function would be used in order to get the Tax Rate (in cell B3) for the entered income (in B2)?
VLOOKUP
A calculated field must reside in the ________ area of the pivot table.
Values
In Excel, a/an ________ is created or run to retrieve text or data from a Web Page.
Web Query
When you try to import data from MS-Access into Excel, which of the following is TRUE?
You can import a table or a query
Regarding the search-and-replace features provided by the "Find and Replace" dialog box, what is NOT true?
You cannot replace a type of formatting in a cell with another type of formatting.
System-wide (i.e., all of Windows)
You must enter it manually
How can you insert a header or footer into the workbook?
a and c
In Step 2 of the Query Wizard, you specify which data rows should be included in your query output. How is the criteria entered?
a criterion for each column
If one attempts to format a serial number that falls outside the range of supported dates, the value displays as __________.
a series of hash (#) marks
In Excel, if one enters a time greater than ten thousand hours, it is interpreted as ________ instead of a time value.
a text string
Please see the attached workbook. The formula in cell C4 is "=B4 + (B4 * $C$1)". Formulas in range C4:C7 use a / an _________ reference to cell C1.
absolute
In Excel, a parameter query is a query that prompts for ________ every time it is run.
an input
To remove a single individual field from a report, _______ the check box beside the field name in the PivotTable Field List.
clear
A calculated item must reside in the ________ labels, ______ labels or ___________ area of a pivot table.
column, row, filter
In Step 1 of the Query Wizard, you select database _________ that you want to appear in your query.
columns
A Web query contains information about the path to the ___________ and can pull the __________ data directly from that source.
data source, updated
HLOOKUP and VLOOKUP are both _________ in Excel
functions
While importing data from a web page into Excel, creating a refreshable Web Query is used to ______.
keep data dynamic or updateable
In Excel, dates are stored as _________.
numbers
A _________ is a graphical data representation of data displayed in a pivot table.
pivot chart
Which of the following is NOT one of the VLOOKUP function arguments?
row_index_num
