CCR 2 MIS 112
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 is the file format (file extension) used when you save an Excel workbook as a single file webpage?
.mht or .mhtml
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
What is the correct order of steps to be followed when you use a query wizard?
1. choosing the columns,2. filtering data,3. sort order, and4. finish
Examine the attached workbook. The data represents the amount of money a customer has spent, in a quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet. For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. For the product 'Carnarvon Tigers', what is the average revenue (sales in dollars) for Qtr 1?
150
For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters.
255
Please see the attached workbook. When you examine the worksheet with the "Manage Rules" (conditional formatting) command, how many rules does the worksheet have?
4
What does the following formula return? =FIND("m","Big Mama Thornton",1)
7
To display today's date in the format like "Today is Friday April 4, 2014", which formula should you use?
="Today is "&TEXT(TODAY(),"dddd, mmmm d, yyyy")
Which of the following options, when used as a formula in the Custom Data Validation criteria for cell range A1:A10, enables the user to enter a value in A1:A10 only if it's greater than or equal to the value in A1?
=A1>=$A$1
Please refer to the attached screen capture. Which of the following formulas will correctly calculate simple interest?
=D5*D6*D7
20130315 represents a date March 15, 2013. To convert this string to an actual date, which of the following formulas can be used? (Assume the data 20130315 is in cell A1)
=DATE(LEFT(A1,4), MID(A1, 5, 2), RIGHT(A1,2))
What formula displays the value of B5 stored in a file named "example.xslx" in a different directory called "TEST" in the "C:\" drive?
=HYPERLINK("C:\TEST\example.xslx",B5)
Examine the attached workbook. Country.xlsx Which of the following formulas, when entered in cell B3, will give you the CURRENCY of the country CHAD?
=INDEX(I2:I239, MATCH("CHAD", D2:D239, 0))
Please see the attached workbook. If you have to lookup the supplier of the item C43 in the given array of data, which of the following formulas will you use?
=LOOKUP(B1,H2:H7,I2:I7)
What is one of the DRAWBACKS of using a pivot table?
A Pivot Table does not update automatically when information changes in source data
To extract a student's first name (e.g., "Herbert") from their full name (e.g., "Herbert Smith"), which of the following functions will you typically use?
A combination of FIND() and MID()
A Loan amortization schedule in Excel is :
A table of values that shows various types of information for each payment period of a loan
#NAME? error occurs under which of the following conditions?
All of the options provided are correct
If you want to apply a date format to serial numbers, which of the following choices will work for you? Assume you have already done the following steps: selected a cell with the serial number, gone to the Home tab, within that the Number group, clicked the drop-down arrow in the Number format box)
All of the options provided will work
What command would you use to import a CSV file?
Choose Data --> Get External Data --> From Text
Which key combination can a user press to toggle between formula view and normal view within Excel?
Ctrl+`
How do you pick the Data Validation option that provides the user with a warning (instead of completely stopping data entry) when a validation check is violated?
Data(tab) -> Data Tools (group)-> Data Validation -> Error Alert -> Style: Warning
When a header or footer section is activated, you can insert any of several element codes from the Header & Footer Elements group. Each button inserts a code into the selected section. What is the purpose of the following code: &[Time] ?
Displays the current time
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.
How do you change the calculation mode while dealing with a calculation intensive data table?
Formulas (tab) -> Calculation (group) -> Calculation Options -> Automatic Except For Data Tables
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
What function helps ensure that the formula will continue to reference the intended cells if the pivot table layout is changed?
GETPIVOTDATA
Which function is used to create a shortcut that opens a document stored on a network server, an intranet, or the Internet?
HYPERLINK()
Which of the following is one of the ways to create a Pivot Chart?
Insert (tab) -> Charts (group) -> PivotChart
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()
Please see the attached workbook. Is =VLOOKUP(E3,TABLE1,3,FALSE) a valid lookup function when used in cell E4?
No: because the table has only two columns but the function refers to column 3
Please see the attached workbook. Which LOOKUP function can be used in order to get the minimum Quantity necessary (in cell E2) for the entered Discount (in cell E3)?
None of the above
If you want to trace cell relationships for formulas, which of the following commands / options could you use?
None of the options provided is correct
______ cells are by default disregarded when data validation rules are created. These cells can be included in the validation rule by unchecking the: "Ignore blank" checkbox.
None of the options provided is valid
How can you define a Print Area after selecting the required cells on the worksheet?
On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area.
Which financial function returns the loan payment (principal plus interest) per period, assuming constant payment amounts and a fixed interest rate?
PMT()
When you print a worksheet, cell gridlines do not usually get printed. If you want your printout to include the gridlines, which of the following options would you choose?
Page Layout (tab) -> Sheet Options (group) -> Gridlines -> Print
_______ refers to the non-printing areas along the sides, top, and bottom of a printed page. You do not see data from worksheet cells here, but may see a header or footer.
Page Margins
Which of the following is TRUE about Web queries?
Performing Web queries copies information from an HTML file into the current workbook
Examine the attached workbook. The data represents the amount of money a customer has spent, in a quarter, to buy a product. Create a PivotTable based on the (entire) table of data in the Sales worksheet. For Row Labels, choose the Product column. Include Columns C, D, E and F in the Values-area. Which quarter saw the highest revenue (sales in dollars) for the product 'TOFU'?
Qtr 2
______________ replaces part of text string that appears in a specific location within a string. Hint: This function can be used when you know the character location of the text to be replaced.
REPLACE()
With regards to Depreciation Calculation functions in Excel, which of the following are true about the function VDB()?
Refers to the Variable-declining balance function. Computes the depreciation of an asset for any period (including partial periods) using the double-declining balance method or some other method you specify.
To avoid printing more pages than necessary in Excel, you can use the following command: ________ .
Set Print Area
Which of the following is NOT one of the worksheet views offered by Excel?
Simplified Layout
______ is an interactive control that makes it easy to filter data in a pivot table.
Slicer
While converting from military time, when would the formula TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) return an incorrect result?
The contents of cell A1 do not comprise four digits
Which of the following is a FALSE statement regarding the 3-color scale?
The minimum and maximum values are the only ones that can be assigned certain colors.
In Excel when calculating loan information, what does the PV() function return?
The time-adjusted value of the original loan amount
Excel formatting tools are used to achieve which of the following?
To make the end result attractive and more functional
There are four boxes/areas below the PivotTable Field List. Which of the following is NOT one of these four boxes?
Tools
To find a working date that falls after a number of days (e.g., 12 business days after some date), you typically use this function:
WORKDAY()
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
Which of the following does not hold true for Watch Windows?
You can see all the formulas in a worksheet and print them for later reference
The #VALUE! error can occur in the following conditions EXCEPT for:
You cut a cell and paste it to a cell that is referenced by other formulas
In Excel, if one enters a time greater than ten thousand hours, it is interpreted as ________ instead of a time value.
a text string
What is the use of the following data validation formula: =A2>A1 ?
accepts values for cell A2 that are greater than the value of cell A1
If there are two or more name / value pairs passed in a URL query string, separate them with the following character: _____________ .
ampersand (&)
In Excel, a parameter query is a query that prompts for ________ every time it is run.
an input
Consider the HLOOKUP function in Excel. If the "range_lookup" argument is set to TRUE, we need to sort the first column of the range in ____________ order
ascending
Please see the attached workbook. In cell A2, how is the data validation done?
by checking if the number of characters is between 4 and 20
When working with Pivot Tables you can remove all the fields from the report so that you can start over. To do this, from the Excel Ribbon, on the Analyze tab, go to the Actions group. Then click the arrow on the Clear button, and select ______.
clear all
Which of the following can be retrieved from a web page using web queries?
data in a table
When you enter "FALSE" for the range_lookup parameter in HLOOKUP, you are telling Excel that you want to find a(n) ________ match
exact
When you create a PivotTable report, each column of your source data becomes a _____ that you can use in the report.
field
A pivot table calculated field consists of a calculation that involves other _______.
fields
A #REF! error occurs in Excel when a formula uses a / an ________ cell reference
invalid
Which of the following options best describe what the following formula would do? =VLOOKUP(D2,PAGES!A2:B10,2,TRUE)
perform a vlookup in a different worksheet which does not have an exact match
A _________ is a graphical data representation of data displayed in a pivot table.
pivot chart
A date filter in Pivot Tables is used to ____________.
specify a time period for which you need the data
The =TODAY() function is used:
to insert the current date which is updated each time a workbook is opened or recalculated
