MIS 112 Final Problem Sets

What is the file format (file extension) used when you save an Excel workbook as a single file webpage?

.mht or .mhtml

What is the correct order of steps to be followed when you use a query wizard?

1. choosing the columns, 2. filtering data, 3. order, and 4, finish

Please refer to the attached workbook. What is the total new deposit amount on Sep - 08 in North County branch>

196188

Please see the attached workbook. The formula =LOOKUP("Mary", A2:A5, C2:C5) returns what value?

28

Please see the attached workbook. What value of tax rate is returned when the entered income is $22,566?

28%

Please see the attached workbook. What tax rate value is returned when you perform a VLOOKUP with an entered income of $45,000?

31%

Please choose the formula that will calculate the future value of a $6,500 investment at 4.55 percent compounded continuously for 4 years

=6500*EXP(4.55%*4)

Assume cell A1 contains the text Micro and cell A2 contains the text Age, which of the following formulas will return MicroAge

=A1&A2

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))

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

RATE()

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

REPT()

Which of the following BEST applies to the INDEX function?

You can use it with the MATCH() function to do reverse lookups when the data is not sorted (e.g., to find the ProductID for a product named "Chocolate Cake", assuming product names are not sorted)

Which of the following is the correct formula for converting the hexadecimal argument "3BC" to its decimal equivalent?

=HEX2DEC("3BC")

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)

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

If there are two or more name/value pairs passed in a URL query string, separate them with the following character: _________.

ampersand (&)

While creating a parameterized web query the question mark (?) is used to ______.

begin the URL query string

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

Please see the attached workbook. The formula in D2 that is copied down the column is "=IFERROR((C2-B2)/C2"")". Excel displays a/an _________ string if the formula results any type of error.

empty

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

HLOOKUP and VLOOKUP are both __________ in Excel

functions

What happens when you click the icon in a Slicer's upper-right corner?

it removes the effects of filtering

A _______ is a graphical data representation of data displayed in a pivot table.

pivot chart

When do you use the following sequence of commands: PivotTable Tools -> Analyze -> PivotTable -> Options -> Generate GetPivot Data?

prevent Excel from using GETPIVOTDATA function

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

All manual. page breaks are shown as _____ lines instead of ______ lines (when you use the view mode of Page Break Preview).

solid, dashed

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

trailing space

A _____ data table in Excel shows the result of a single calculation for different values of two input cells

two-way

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

two-way link (updates made in the Pivot Chart are reflected in the PivotTable)

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. Uf the 'amount' for the given date is to be retrieved form the given range of data (I4:K7), which of the following formulas should be used?

=INDEX(I4:K7,MATCH(B2,14:17,0),3)

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)

Converting a string (e.g., an email address) to purely lowercase, can be done using which of the following functions?

=LOWER()

Which of the following formulas rounds a time in cell B2 to the closest minute? (You can assume relevant cells have Excel's Time formatting?

=ROUND(B2*1440,0)/1440

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)

What changes will you make in the formula =VLOOKUP(D2,PAGES!A2:B10,2,FALSE) so that it checks for an approximate match?

=VLOOKUP(D2,PAGES!A2:B10,2,TRUE)

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 the date is a Sunday? Hint: Date format: MM/DD/YYYY

=WEEKDAY(A1)=1

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

Which of the following is TRUE for Pivot Tables?

A Pivot table can be sorted by any column selected in the Pivot table field list

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

What does it mean when Excel displays the following error? #DIV/0!

A number is divided by zero!

How do you group Pivot Table items manually?

After creating the pivot table, select the items to be grouped and then choose Analyze (tab) under PivotTable Tools and select Group (group) -> Group Selection

Using the Normal view for a workbook, which of the following actions can be performed for Page Breaks?

All of the Above (Insert Page Breaks; Remove Page Breaks; Reset All Page Breaks)

An Excel formula may return which of the following errors?

All of the above (#REF!, #DIV/0!, #VALUE!)

Which of the following is TRUE about Pivot tables?

All of the answers provided are TRUE (You cannot add new rows or columns to the Pivot table; You cannot change any of the calculated values; You cannot enter formulas within the pivot table)

Which of the following file types can Excel import?

All of the file types (XML Files [.xml], Comma separated values [.csv], Microsoft Access [.accdb])

How can you avoid the "#NAME?" error value?

All of the options provided are correct (If the formula uses a label in a formula, make sure labels are allowed; Enclose any text in double quotation marks; Make sure all range references in the formula use a colon)

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 (Short date; Long date; More number format)

Choosing: Conditional Formatting -> Color Scales -> More Rules -> Format Style: allows you to change formatting rules for a color scale. Which of the following formatting options are available?

All options: 2-Color Scale, 3-Color Scale and Data Bar are available

Which of the following is TRUE about icon sets?

An icon can be used to display a trend graphically

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

Both A and B (computes depreciation at a fixed rate; it's arguments are: cost, salvage, life, period, [Month])

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

Both a) and b) (Home (tab)-> Number (command group) -> Number Format -> General; Press Ctrl + Shift +~)

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. In the EmpID table, enter the empID in cell 12345. Given the error message, what will you change in the Data Validation dialog box > settings (tab) for 12345 to be a valid entry?

Change the settings to a whole number > less than > 100000

Which of the following is True regarding the HTML files that Excel creates?

Changes to graphs and formulas cannot be made to an HTML file created from Excel.

How do you set up a data validation (using the data validation dialog box) that restricts data entry to a text of a specific length?

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

What is the purpose of the function TIMEVALUE()?

Converts a time in the form of text string (e.g., "18:45") to a serial number

When entering/editing an Array formula, press ________ to enter the formula (or Excel does not recognize the formula as an Array formula).

Ctrl + Shift + Enter

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

Ctrl+K

Issuing the Print command (in Excel) with a mouse takes three clicks. A slightly more efficient method is to press _______ and then click the Print button

Ctrl+P

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

D

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

Please refer to the attached workbook. Column A has first and last names. The goal is to extract the first name in column B as shown in cell B1. With B2 as the active cell, which of the following is the correct set of steps to achieve that?

Data --> Data Tools --> Flash Fill

Please see the attached workbook, Sheet1. What happens when you apply a theme on Sheet1 of the Excel sheet by going to Page Layout -> Themes

The theme is applied to all the sheets in the workbook

In Excel when calculating loan information, what does the PV() function return?

The time-adjusted value of the original loan amount

What is the output of the following command sequence: click the Analyze tab under PivotTable tools -> Actions (group) -> Clear -> Clear Filters?

This removes all the filters at once

Please refer to the attached workbook. What would you do if you want to filter records in the report so that you get information only for the state of AZ?

To filter the report, click the arrow next to Row Labels and check the 'AZ' options from the menu that appears

Please see the attached workbook. The highest 5 salaries for employees can be viewed using which of the following options under Conditional Formatting?

Top 10 Items under Top/Bottom Rules

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

Which one of the following is NOT a formatting option in the Web Query Options dialog box?

XML formatting

Is it possible to change the direction of bars in DATA BARS conditional formatting?

Yes

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

Please see the attached workbook. In the EmpID table, enter the salary in cell H8 as 100. Given the error message, why do you think Excel does not let you input that value?

Data validation settings require the salary value to be greater than the hours worked value, 120.

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

Displays the workbook s complete path and filename

Which older version of Excel file can Excel 2016 NO LONGER open?

Excel can open all of these files (Excel 4, 97, 2000)

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

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

Light, Medium and Dark are the categories of styles found under the ______ command [within the Home (tab) -> Styles (command group)]

Format as Table

Please see 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 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

In Excel, if one enters a time greater than ten thousand hours, it is interpreted as _________ instead of a time value.

In Excel, the ________ function calculates the difference between 2 dates excluding weekend days,

To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table and then choose _______________.

Insert (tab) -> Filters (group) -> Slicer. The insert Slicers dialog box appears

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 using HLOOKUP()?

It usually reduces duplicate data storage

Which of these dates will Excel store as a text string rather than a serial number?

June 23 2014

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

Link_location is required, Friendly_name is optional

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

Mr. John Q. Public

In Excel, the ________ function calculates the difference between 2 dates excluding weekend days.

NETWORKDAYS()

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

When you use the HLOOKUP() function, which of the following parameters represents the row number from which the matching value will be returned?

None of the options provided is correct

Please refer to the attached workbook. What happens if the following value filter is added t the existing report? Value filter: Sum of Sales does not equal 5000

Nothing happens. Both the records remain.

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 Qtr 3, which product was brought by the highest number of customers?

Raclette Courdavault

Which of the following is true with regards to row and column titles and headers?

Row and column titles describe field names in a table or list while headers appear at the top of each page

_______ locates a substring in another text string and returns the start position of the substring.

SEARCH()

Which of the following is TRUE about Pivot Charts?

Slicers also work with pivot charts

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