MIS 112 Final

which of the following formulas can be used to calculate Sophie's total score in both the exams?

=B4+C4

To calculate the percentage of the total widget sales made by the top 5 employees, which of the following formulae would you enter in cell "C22" (shaded purple)

=C20/F8

Which formula can be used to find: How many songs were sung by singers whose last names contain exactly six letters?

=COUNTIF(B2:B23,"??????")

Which formula can be used to find: How many songs were sung by singers whose names begin with S?

=COUNTIF(B2:B23,"S*")

Examine the attached worksheet. Which of the following formulas, when entered in cell B12, will give the total number of items for which there was no discount offered (i.e., discount is 0)?

=COUNTIF(E2:E8, 0)

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

Suppose you deposit $100 at the beginning of each month (for 12 months) into an account that pays 3.5 percent annual interest compounded monthly. Which formula calculates the future value of your series of deposits?

=FV(3.5%/12,12,-100,,1)

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)

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)

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

Which of the following formulas is used to determine the date of the Sunday that was most recent?

=TODAY()-MOD(TODAY()-1,7)

Which two wildcard characters are supported by the "Find and Replace" dialog box in an Excel worksheet?

?, *

Which of the following does the search string ~?NULL~? match when entered in the find and replace dialog box?

?NULL?

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 Loan amortization schedule in Excel is :

A table of values that shows various types of information for each payment period of a loan

Please see the attached image. What is the range of the date axis in Sparkline chart?

A1:A11

In an Excel worksheet, what column name comes directly after Column Z?

AA

You are reviewing a Spreadsheet that records daily sales for Burgers, Hotdogs and Chili. Monday of last week shows exceptionally high sales for Burgers. You want to email the spreadsheet back to your assistant so he can look into whether the data was correctly entered. Which of the following methods should you use to explain your request to your assistant?

Add a comment to the sales figure cell, and explain to your assistant what you want him to do

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

Which of the following is the keyboard shortcut that creates a new blank workbook in Excel?

Ctrl + N

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

Which of the following options scrolls the screen so that the active cell is visible?

Ctrl+Backspace

What is the shortcut to open the Find and Replace dialog box with the "Find" tab open?

Ctrl+F

In Row 2 (Col B), which of the following formatting types has been applied to "3419.25041" to make it appear as "$3,419.25" ?

Currency

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

datavalidation.xlsx 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.

What is metadata?

Descriptive information about a workbook

Which of the following Excel tabs is NOT visible in a worksheet by default?

Developer

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

When you apply currency formatting to a Table column and then add a new row to the table, what happens to data entered in the new cell?

Excel applies currency formatting to the new value in that column cell.

What would happen if you used a formula to perform arithmetic operations on dates, but one of the dates was typed in a format that Excel does not recognize as a date?

Excel will display an error value

Which cell is currently active in the figure?

F4

While using cell references in Excel, what key is used to change the type of the reference?

F4

Which of the following command sequences will allow you to save a copy of your workbook with a different name?

File, Save As

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

The _____________ command can extract specific pieces of information from cells, as long as the values are stored consistenty.

Flash Fill

Examine the attached workbook. If you format a chart element with the font seen in Chart 2, how can you reset it to its original state?

Format tab (chart tools) --> Reset to Match Style (under the "Current Selection" group) Right click on the chart --> Reset to Match Style Either of the previous options (resetting through the menu or the right-click options work) Home tab --> Wrap Text, followed by, Paste

Which of the following statements is true? They all relate to the formula: =IF( OR(Category="Pasta",Category="Soups"), Inventory_Cost,0 )

In this formula, the second statement in the OR (i.e., checking for the category ="Soups") is only checked/evaluated if the first part (i.e., checking if category ="Pasta") evaluates to FALSE

Which of the following is one of the ways to create a Pivot Chart?

Insert (tab) -> Charts (group) -> PivotChart

Please see the attached Excel workbook. Suppose you are on Sheet1 and you have selected cells A5:B5. What happens when you insert a background image through the following sequence of steps? Page Layout (tab) -> Page setup (command group) -> Background?

It adds a background image to the currently active worksheet

Which keyboard shortcuts can be used to move the active cell one colunn to the left?

Left arrow or Shift + Tab

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

MATCH()

What is the name of the company that created Excel?

Microsoft

Which of the following is NOT an advantage of using defined range names to work with ranges in Excel?

Moving to areas of the worksheet is controlled

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

NPER()

Which of the following is FALSE about Pivot Table Slicers?

None of the answers provided is false

How do you clear/remove a Print Area that has already been defined on the worksheet?

On the Page Layout tab, in the Page Setup group, click Print Area, and then click Clear Print Area.

If you need to take a printout of a report, how can you specify the paper size you re using?

Page Layout (tab) -> Page Setup (group) -> Size

A calculated item, unlike a calculated field, does not appear in the _________ Field List.

PivotTable

In the following formula, Excel sums up the data in which range? =SUMIF(Products!I2:I50,"=Canned Fruit & Vegetables",Products!F2:F50)

Products!F2:F50

The _____________ function is used to return a specified number of letters or characters from the end of a text string.

RIGHT()

Suppose your worksheet has a comment in cell A1 and a comment in cell B5. Your active cell is B1. Which command can you use to navigate to the comment in cell B5?

Review (tab) -> Comments (command group) -> Next

What Excel element is shown in the red box?

Ribbon

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

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

Save a query so that you can reuse it

Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ?

Select cells A1 through C4 in Sheet1 -> Copy -> Go to Sheet2 -> Paste -> Transpose

What does selecting the option "Current region" within the: Go To Special Dialog Box (Home (tab) -> Editing (command group) -> Find & Select) do?

Selects a rectangular range of cells around the active cell

To view or edit a comment, you can select the cell where a comment has been attached and press the following keyboard shortcut: __________.

Shift+F2

Which of the following is a shortcut key used to calculate only the formulas in the active worksheet?

Shift+F9

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 of the following is TRUE about sorting a table?

Sorting a table re-arranges the rows based on the contents of a particular column.

If you want to use commands to customize Sparklines to highlight certain parts of the data, which of the following options would you select?

Sparkline Tools -> Design (tab) -> Show (group)

After you create a Sparkline, changing color is easy. Use the controls in the _______________.

Sparkline Tools -> Design (tab) -> Style (group)

Unlike charts on an Excel worksheet, _________ are not objects. Instead these are tiny charts in the background of a cell.

Sparklines

While comparing two sheets using the "View Side by Side" feature, there is an option which lets you scroll the two sheets in sync. What is that option called ?

Synchronous Scrolling

Microsoft Office Excel can automatically generate a data form (which allows for convenient data entry, among other things) for your range or _______________.

Table

Which of the following is NOT one of the places to adjust print settings?

The Alignment dialog box

To create Sparklines, users do NOT need to enter or specify which of the following: _____.

The count and sum of cells

Examine the attached Excel file. In row 4, we have two different ways of displaying the August 25 date. Which of the following is TRUE about these dates?

The data stored in the two cells A4 and B4 are identical

What part(s) of a table can be selected?

The entire table, an entire row, or an entire column from a table can be selected

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

The formula: =CHAR(CODE("A")+32) returns which of the following?

a

Please see the attached workbook. What does the small red triangle on the upper right corner of cell B1 denote?

a comment exists for that cell

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

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

What form of LOOKUP is used when you have a small list of values and the values remain constant over time?

array form

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

Which of the following does the function =NOW() return?

current date and time

Example_1.xlsx 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 TRUE of the "Protect" option in an Excel Workbook?

it helps prevent unauthorized users from copying existing worksheets it helps prevent unauthorized users from revealing hidden sheets it helps prevent unauthorized users from inserting new worksheets All of the above options are true

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

lookup_value

In a Pivot Table, the names of the fields for the report come from ________.

none of the answers provided is correct

For a range called Data, what does the function =COUNTIF(Data, TODAY()) return?

number of cells containing the current date

For a range called Data, what does the function =COUNTIF(Data, "<>0") return?

number of cells not equal to 0

You select a cell with data "120" in an Excel worksheet and press the "Delete" key on your keyboard. Which of the following holds TRUE?

only the contents of the cell are removed

Worksheet background images are for ______ display only, the images do not appear when the worksheet is ________.

onscreen, printed

A group of cells is referred to as which of the following?

range

The COUNTIF() function used for single-criterion counting formula takes two arguments (or parameters) _______ and ______.

range (and) criteria

What is the Skip blanks option (under Excel Paste Special option) used for while copying a range to another area?

to prevent blank cells in the copied range from overwriting existing data

Example_1.xlsx 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

Which of the following chart-related items identifies general trends in your data?

trendline

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

two-way

Which of the following methods will keep a heading at the top of a table visible while you scroll through rows lower down in the worksheet?

use Freeze Panes

Fill in the blanks: Conditional formatting effects help us _______ values in cells making it easier to locate data, find exceptions and spot ______ quickly.

visualize, trends

The ____ command is used to display text on multiple lines in a cell.

wrap text

Which of the following is NOT a tool in Analysis ToolPak for Excel?

y-Test

The Pivot Table Field List shows which of the following?

Columns from the source data and Groups defined in the Pivot Table

What is the result of the following expressions (formulas), respectively? = (10 - 6) * 10 and = 10 - 6 * 10

40 and -50

What will be the result of this formula =IF(A1<100000,A1*5%,A1*7.5%) , if the cell A1 has a value of 90000?

4500

Please refer to the figure. How many worksheets are contained in the workbook shown?

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

Which of the following functions can be used to count specific types of errors?

COUNTIF

datavalidation.xlsx Please see the attached workbook. In the Student Rank table, in cell F19, type Rachel instead of Dennis. Why do you receive an error?

Cell F19 can only take text that begins with the letter 'd'.

Which of the following is a way of accessing the "Evaluate Formula" dialog box that helps debug a formula?

Choose Formula ->Formula Auditing ->Evaluate Formula

To gain access to the Table Tools / Design tab in the Excel Ribbon when working with tables, you should do the following:

Click anywhere on the table

Please see the attached Excel workbook image in answering this question. In order to sort the 'Salesperson' column in a descending order, which of the following steps is correct?

Click on any cell containing Salespersons data, from the Ribbon pick the Data tab, and then click on the "Sort Z to A" command button

Examine the attached workbook. To move "Chart 1" in "Sheet1" to a new Excel worksheet named "MyChart", which options will you use?

Click on: (Chart Tools) Design tab --> Move chart (Location) --> New Sheet (option)

dataLookup_eg8.xlsx Please see the attached workbook. What would you get in E4 if you typed in "South-Africa" in cell E3?

"an error because the formatting of the word "South-Africa" is different than the one found in the table

The Excel function: ISERR() returns TRUE if the cell contains any error value except which of the following?

#N/A

Which of the following error codes indicates that a problem with a value exists? For example, a negative number was specified by the user where a positive number was expected.

#NUM!

In the attached image, examine the formula in cell "E9." What result will this formula produce?

#VALUE!

Calculate the total yearly sales for the West Region and the South Region in cells "F5" and "F7" (shaded in orange). What is the total widget sales for all regions for the year?

$24,914

What is total widget sales for the West Region for the year?

$5,129

Which of the following is a VALID format for a URL query string in Excel?

---URL---?<Name>=<Val>

Which of the following is the file format of the Excel 97-2003 workbook?

.xls

By default, what is the extension used when you save a workbook in Excel 2016?

.xslx

Within the "Number" tab, how many categories are available for displaying numbers?

12

With a single IF() function, how many actions can you ask Excel to carry out?

2 (i.e., an action if the criteria is true, or an an action if the criteria is false)

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)

Which of the following statements about Trace Precedents are correct?

All of the options provided are correct

Which of the following actions can be performed on objects placed on the drawing layer with no effect on any other elements in the worksheet?

All of the options provided are correct (move resize delete)

How will you refresh the query after moving the cell pointer anywhere within the external data table in the worksheet?

All the options in this question are valid

The =SUM() function does which of the following in Excel?

Allows you to total up numbers in a specified range of cells

While working with Shapes in Excel, which command group contains commands to adjust the "stack order" of shapes, align shapes, group multiple shapes and rotate shapes?

Arrange

Suppose you have a range of cells with numbers and a few blank entries. If you sort the range in ascending order (smallest to largest), where will the blank cells be located after sorting?

At the end

In the Top Salespersons list, which feature can be used to populate the numbers "3," "4," and "5" in cells "A17," "A18," and "A19," respectively?

Autofill

Which type of chart does not allow format changes to the Plot area?

Bar chart None of the (other) options provided is correct Treemap Thermometer chart

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

Both A and B (Computes depreciation at an accelerated rate. Depreciation is highest in the first period and decreases in successive periods. Its arguments are: Cost, Salvage, Life, Period, [Factor])

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

CONCATENATE()

Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?" You may refer to the image in the question.

COUNT()

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

The HLOOKUP and LOOKUP functions can be found under Formulas (tab) ->_________.

Function Library (command group) -> Lookup & Reference

How does a missing data point show in a Sparkline graphic?

Gap

After pressing "Alt + H" while in an open Excel worksheet, a number of Key Tips are displayed. Which ribbon tab do these Key Tips relate to?

Home

If there are negative values in your data, you can emphasize this by showing a(n) __________ axis in your sparkline.

Horizontal

Which of the following is FALSE about using the conditional formatting option to display an icon the cell?

Icon set rules cannot be modified.

Which of the following statements is TRUE?

If I have a worksheet with student names (First, Middle, Last), you can use the COUNTA() function to find how many student names are on the worksheet

If a pivot chart is created and the underlying table is deleted, what occurs?

The pivot chart remains.

The formula =COUNTIF(range, "*budget*") will count the number of cells in the specified range containing the word "budget" in certain positions within the text. Which of the following best describes the cells counted?

The word "budget" can appear anywhere within the cell.

Consider the following formula: =SUMIFS(Inventory_Cost, Category,"=Beverages", Suppliers,"*4*"). Which of the following statements is TRUE?

Two criteria are checked for this formula to include a value in the total: a) the product category must be "Beverages", and b) the supplier must be "4"

What of the following is FALSE about filtering a table?

When you copy data from a filtered table, both visible data and rows hidden by filtering are copied.

If you copy the whole cell "B8" (not only its formula) and paste it into cell "D8" (shaded in green), will it produce the correct total sales value for Quarter 3?

Yes

Is it possible to get the data back after deleting it using keyboard ?

Yes

Would you be able to apply the "watch window" option in multiple sheets at the same time ?

Yes

You can use a data Form to add, find, and edit rows in a range or table. Is this correct?

Yes, all these are valid form operations

If you copy cell "B6" and paste it as a formula in cell "C6," will it produce the correct total for Year 2?

Yes, the formula range will be adjusted automatically

Please refer to the attached workbook. The ______ , ________ and ________fields are in the Column, Rows and Values sections respectively.

branch, date and amount

datavalidation2.xlsx 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

After you create a chart, which of the following actions can you perform on it?

change the chart type, formatting, and data series