MIS112 (2nd half of course)

What does Excel display when you enter the formula =DATE(2014,13,1)?

"1/1/2015"

The HLOOKUP function works like VLOOKUP function except that the lookup table is arranged ___________ instead of __________.

"horizontally, vertically"

What result does the following formula display? =DOLLAR(5742.999,2)

$5,743.00

Please refer to the attached workbook. How many Savings type accounts were opened in Central and West side branches (combined)?

134

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. What is the total revenue (sales in dollars) generated in Qtr 4 for the Products 'Aniseed Syrup', 'Boston Crab Meat' and 'Camembert Pierrot'? Pivot_table_test.xlsx

5156

The Excel CODE function returns the character code for its argument. What does the following formula return? =CODE("B")

66

Which of the following formulas returns the number of days in the corresponding month for a date in cell B2?

=DAY(DATE(YEAR(B2), MONTH(B2)+1,0))

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)

Please see the attached workbook. How would you avoid the displayed error created by =B2/C2 ? (You should try the formulas shown as options for this question, to check for the correct answer)

=IF(C2=0," ", B2/C2)

Please see the attached workbook. If you have to use a formula to retrieve the age of the student from the given array of data, which of the following formulas will you use?

=INDEX(J4:L8,3,3

Which of the following options, when used as a formula in the Custom Data Validation criteria for cell A1, will ensure that the cell entry is a date, and that thedate is a Sunday? Hint: Date format: MM/DD/YYYY

=WEEKDAY(A1)=1

What is one of the DRAWBACKS of using a pivot table?

A Pivot Table does not update automatically when information changes in source data

A pivot chart is which of the following?

A graphical representation of a data summarized in a pivot table

If Row 15 needs to be on the first row of a new page and only a horizontal page-break is needed, what cell should be selected to create a page break?

A15

Which of the following are TRUE in the context of using VLOOKUP?

All answers provided are correct

An Excel formula may return which of the following errors?

All of the above

Which of the following is TRUE about a Watermark in Excel?

All of the above statements are TRUE.

There are two options if you want to save an Excel workbook, so that it can be viewed on the World Wide Web. What are the two options?

An HTML file and a single-file web page

With regards to Depreciation Calculation functions in Excel, which of the following are true about the function DB()?

Both A and B

To view calculated fields and calculated items in a pivot table, which of the following can you do?

Choose PivotTable Tools -> Analyze -> Calculations -> Field, Items & Sets -> List Formulas

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a decimal number within limits?

Click the Settings tab (in the Data Validation box). In the Allow box, select 'Decimal'. In the Data box, select 'between', and set the minimum and maximum.

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.

What is the purpose of the function TIME()?

Converts the input hours, minutes & seconds to a serial number

If you need to create several different printed reports from the same Excel workbook, setting up the specific settings for each report can be a tedious job. You can simplify the process by using the ______________ feature that enables you to give names to various views of your worksheet, and you can quickly switch among these named views.

Custom Views

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: &[Tab] ?

Displays the sheet's name

Where do you find the command to add a pivot table on your worksheet?

Excel Ribbon: Insert (tab) ->Tables

When working with data returned from a Query Wizard, you can adjust a variety of properties of an external data range. This can be done using the ______________.

External Data Properties Dialog Box

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 that starts with 'T' for 'City' column.

From the Column to Filter list, select City. In the right panel, select 'begins with' from the first drop-down list and then type 'T' in the second drop-down list box

Please see the image below. Which of the following conditional formatting styles is used in creating rules for the "Defects" column?

Icon sets

When (or why) does Excel draw arrows to indicate an error source?

In Tracing Error Values

If you need to display lots of text in a worksheet, you can consider using a text box. How will you insert a textbox?

Insert (tab) -> Text (command group) -> Text Box

The basic formula for computing simple interest is:

Interest = Principal * Rate * Term

What is the purpose of the Text to Columns command?

It parses strings into their component parts.

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

What statement about the two Hyperlink arguments Hyperlink(link_location,[friendly_name]) is TRUE?

Link_location is required, Friendly_name is optional

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.

Which function returns the number of payment periods for a loan, given the loan's amount, interest rate, and periodic payment amount?

NPER()

Please see the attached workbook, Sheet3. When you print the worksheet with graphic background what happens?

No background is printed, even though the background is displayed onscreen.

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

Which of the following is FALSE about Pivot Table Slicers?

None of the answers provided is false

By default, Excel prints worksheets in ___________ orientation

Portrait

Which of the following is TRUE about Pivot Charts?

Slicers also work with pivot charts

Which of the following options is TRUE about the different lookup functions?

The CHOOSE() function returns a specific value, corresponding to the index number, from a supplied list of values.

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.

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

In Excel, a/an ________ is created or run to retrieve text or data from a Web Page.

Web Query

What is an advantage of using formulas instead of Autofill to create a series of dates? (e.g., for a work-week schedule)

When the first date is changed, the others update automatically

If one attempts to enter a date that falls outside of the range of supported dates, Excel will interpret it as _______

a text entry

Using the =NA() function in a cell, makes it clear that data is not _______ and hasn't been ______ accidentally.

available, deleted

To remove a single individual field from a report, _______ the check box beside the field name in the PivotTable Field List.

clear

When you specify which rows should be included in your query (selection criteria), you are satisfying which step of the Query Wizard?

filter data

What is the value to be found in the first row of the table while using the HLOOKUP function?

lookup value

By pressing the spacebar, the contents of a cell seem to erase because pressing the spacebar inserts a / an _______ space character

non visible

While using web queries Rich text formatting is typically used if we wish to ______.

reproduce web page data in a format close to that of the original

The Find and Replace dialog box cannot find background color formatting in tables that was applied using __________ or formatting that is applied based on ____________

table style, conditional formatting

What does it mean when excel displays the following error? #####

the column is not wide enough to display contained data or a negative date or time is used

The =TODAY() function is used:

to insert the current date which is updated each time a workbook is opened or recalculated