MIS 112 Final (Modules 5-8)

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

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, and 4. finish

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

1/1/2015

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

Please see the attached workbook. What value does the formula "=VLOOKUP("John",A2:D13,3,FALSE)" return?

2000

For Windows systems, Excel uses the standard 8-bit ANSI character set. This character set consists of ___ characters.

255

If the date serial number of March 15, 2014 is represented as 41713 then how will noon (half way through that day) be represented?

41713.5

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'?

5156

What does the following formula return? =FIND("m","Big Mama Thornton",1)

7

Which of the following options, when used as a formula in the Custom Data Validation criteria for a 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 see the attached workbook. In the Student Rank table, notice that H15 and H19 have the same value. Since two students cannot have the same student ID, what formula would you use in the Data validation settings in this case to make sure duplicate entries are not allowed?

=COUNTIF($H$13:$H$H19,H19)=1

Please see the attached workbook. The result of an HLOOKUP function is "yellow." What is the formula used to obtain this result?

=HLOOKUP("Color",A1:B5,3,FALSE)

If you place the link target in cell C1 on the same worksheet, how will you use an absolute reference to that cell as the link_location in the HYPERLINK formulas?

=HYPERLINK($C$1)

Please see the attached workbook. How would you avoid the displayed error created by =B2/C2?

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

Which of the following formulas is used to determine whether the date in cell D4 is part of a leap year?

=IF(MONTH(DATE(YEAR(D4),2,29))=2,TRUE,FALSE)

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)

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)

Suppose you have a column of file names such as: 001-Chapter1.txt, ..., 015-Chapter15.txt, ..., 104-Chapter104.txt, etc.You want to use a formula to consistently remove the prefix numbers, e.g., make the file names into: Chapter1.txt or Chapter12.txt.Which of the following text functions can you use to make this change?

A combination of: FIND(), LEN(), MID()

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

Which of the following statements is false? You can create a pivot table and a pivot chart using the same data Slicers can be used with Pivot Charts All Excel charting features are available in a pivot chart 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 pivot chart is always based on a pivot table i.e., changes to the pivot chart must be made by changing the underlying table

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 single-file web page

How will you return a cell formatted as a date or time, back to its default or original format?

Both a) and b)

This function combines its arguments into a single text string. It is an alternative to using the ampersand operator.

CONCATENATE()

What command would you use to import a CSV file?

Choose Data > Get External Data > From Text

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 text of a specified length?

Click the Settings tab (in the data validation box). In the Allow box, select 'Text Length', 'equal to', and specify the length.

In Excel in order to avoid incomplete calculation errors and to ensure that formulas are fully calculated, you would press ____.

Ctrl + Alt + Shift + F9

What is the shortcut key to create a hyperlink when you select an object or a cell?

Ctrl + K

What does the following formula return? =CHAR(68)

D

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

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 (command group) -> From Other Sources -> From Microsoft Query), how will you filter the records to retrieve those that have a value 'Tucson' for 'City' column.

From the Column to Filter list, select City. In the right panel, select 'equals' from the first drop-down list and then select 'Tucson' 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 that ends with 'm' for 'Name'.

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

Microsoft Query provides you with a number of features while importing data. Which of the following is NOT an option you can specify or customize through Microsoft Query?

Graphing of data from tabled

You can create your own style through which of the following steps?

Home (tab) > Styles (command group) > cell styles

Which of the following steps are to be followed in order to display the Go to Special dialog box which is an Excel Auditing Tool?

Home > Editing > Find & Select > Go to Special

Which function returns the contents of a cell at the intersection of a specified row and column from a range?

INDEX()

Which of the following functions looks in a one-row or one-column range (lookup_vector) and returns a value from the same position in a second one-row or one-column range (result_vector).

LOOKUP

Which of the following options provides correct syntax for the LOOKUP function?

LOOKUP(lookup_value,lookup_vector,[result_vector])

The HYPERLINK function syntax has two arguments. Which argument has the path and file name to the document to be opened?

Link_location

Which function returns the relative position (in a range / table) of a cell corresponding to a specified value?

MATCH()

What is NOT the purpose of using slicers?

Make table more beautiful

How do you remove a filter in the pivot table 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.

If cell A1 contained the text MR. JOHN Q. PUBLIC, what would the formula (=PROPER(A1)) return?

Mr. John Q. Public

Which of the following is TRUE about Web queries?

Performing Web queries copies information from an HTML file into the current workbook

Which function can be used to make crude/simple horizontal separators between cells?

REPT()

The ____ function is one solution to account for floating point number errors. It also serves to round off values to the desired number of decimal places.

ROUND()

Please see the attached workbook. The result of the formula =VLOOKUP("Apple",A2:B5,2,FALSE) is which of the following?

Red

Please see the attached workbook. Row 5 is hidden and contain 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.

The __________ function will be used as part of a formula to calculate how many days old you are today.

TODAY()

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

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

You ask customer to provide you with inventory usage data to help you plan your production schedule. Which of the following file formats will you ask them to provide data in (so that data can be easily imported into Excel)?

XML

Which of the following is TRUE about the DATEDIF() function?

You must enter it manually

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 the context of the MATCH() function, which of the following is FALSE? The syntax of match is: MATCH (lookup_value,lookup_array, row_offset) MATCH returns the cell contents (e.g., a product name) when it detects two cells with duplicate contents A #VAL error value is returned if the MATCH function is unsuccessful in finding a match All of the options are false

all of the options are false

Which of the following is one of the conditional formatting rules? Highlight cell rules color scales data bars

all the options are correct

How will you refresh the query after moving the cell pointer anywhere within the external data table in the worksheet? Right-click and choose Refresh from the shortcut menu Choose: Data (tab) -> Connections (command group) -> Refresh All. Click "Refresh" in the Workbook Connections dialog box (displayed by choosing: Data (tab) -> Connections (command group) -> Connections ).

all the options in this question are valid

A pivot table and a pivot chart are joined in a two-way link, if you make structural or filtering changes to one, the other _______.

also changes

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

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

available, deleted

If you want to format cells that have values between 50 and 60, which option under 'Highlight Cell Rules (Conditional Formatting)' category would you select?

between

Which of the following concepts are useful in understanding relationships between cells and formulas?

cell dependents

To remove a single individual field from a report, ____ the check box beside the field name in the PivotTable field list.

clear

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a date greater than a specified date?

click the settings tab (in the data validation box). In the Allow box, select 'date'. In the Data box, select 'greater than', and specify the start date.

Please refer to the attached workbook. What does the pivot chart depict?

compares the amount in the three branches on the basis of AcctType

Which of the following is NOT one of the buttons in the Spelling Dialog box in Excel?

conditional formatting

Most pivot tables are created from numeric data, but pivot tables are also useful with some types of non-numeric data. Because you cannot sum non-numbers, this technique involves ____

counting

A Web query contains information about the path to the _____ and can pull the ____ data directly from that source.

data source, updated

Which older version of Excel file can Excel 2013 NO LONGER open? Excel 4 Excel 97 Excel 2000

excel can open all these files

Assuming you have a report that contains multiple products from XYZ company. What Pivot table feature would you use to see that data of any one product?

filter

The _____ command can extract specific pierces of information from cells, as long as the values are stored consistently.

flash fill

What is the use of the following data validation formula: =ISTEXT(A1) ?

forces the cell A1 to accept only text

Please refer to the attached workbook. In Table 1, the Pivot table shows which of the following? Gender is a Column Label Region is a Column Label State is used as a Report Filter State is used as a Report Filter, Region as a Column Label

gender is a column label

If you click outside of the layout area, the pivot table field list ___.

goes away

To make Data Bars easier to view, we can ________ the numerical values in the cell.

hide

Please see the attached workbook. What is the URL Query String for the extract in the attached worksheet?

http://www.imdb.com/

Which of the following is the most compelling motivation for using HLOOKUP()?

it usually reduces duplicate data storage

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

link_location is required, friendly_name is optional

Examine the following VLOOKUP formula '=VLOOKUP(C2,EmpData,2,FALSE)'. What is the name of the argument field represented by C2?

lookup_value

When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned? range lookup lookup_value row_care_param None of the options provided is correct

none of the options provided is correct

Which of the following is FALSE about Pivot Table slicers? Slicers can be moved and resized. The effects of filtering by a particular slicer can be removed. To use a Slicer to filter data in a Pivot Table, you can click one of the slicer value buttons.

none of the provided answers is false

In the following choices, which one is not among the drop down list of the conditional formatting on the ribbon? Number bars Color Scales Top/Bottom Rules Manage Rules

number bars

A ___ is a graphical data representation of data displayed in a pivot table?

pivot chart

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 first field you add to a PivotTable report that does not contain numbers will automatically be added in the _______ part of the report.

row labels

The last step in the Query Wizard, "Finish", lets you do which of the following?

save a query so that you can reuse it

The formula '=NOW()-TODAY()' displays current time as a ___________ without a related date.

serial number

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

Which icon (in data validation error alerts) is used to prevent users from entering invalid data into a cell?

stop

The Find & 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

There are four boxes below the PivotTable Field List. Which of the following is not one of these four boxes?

tools

Please see the attached workbook. The formula in B1 which is copied down the column is "=TRIM(A1)=A1". The formula returns false for (B3) because the text in that cell contains?

trailing space

What best describes the connection between a Pivot Table and a Pivot Chart?

two-way link

A calculated field must reside in the ____ are of the pivot table.

values

Conditional formatting effects help us ____ values in cells making it easier to locate data, find exceptions and spot ____ quickly.

visualize; trends

Please refer to the attached workbook. The _____ and _____ fields are in the Rows and values section respectively.

weekday, amount

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