MIS112 Module 1-4, MIS112 Module 5-8

Please see the attached workbook. Using the formula =B2/C2 what error is displayed?

#DIV/0!

Assume the "range_lookup" parameter in an HLOOKUP function is FALSE. If an exact match is not found, what value is returned?

#N/A

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!

Which of the following error codes indicates that a formula refers to a call that is invalid? This type of error can occur if a referenced call is deleted from the worksheet

#REF!

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

#VALUE!

Examine the attached file. Calculate the total yearly sales for the West Region and South Region in cells F5 and F7. What is the total widget sales for all regions for the year?

$24,914

Examine the attached file. What is the total widget sales for the West Region for the year?

$5,129

Within the number tab, how many categories are avaliable 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)

The formula =COUNTIF(range, "???") returns the number of cells in the range containing exactly_____ characters of text

3

A single cell in Excel can hold up to approximately ______ characters.

32000

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

What would you type in the Find and Replace dialog box (Home (tab)-> Editing (command group)-> find and select) to find all four digit entries that begin with 5 and end with 2?

5??2

If you enter 9/19/2014 25:00:00, how will it be interpreted by Excel? (Note: assume a Short Date format)

9/20/2014 1:00:00am

Examine the attached Excel file. When you copy the formula in cell C3 to F7 , the cell F7 contains which of the following formulas?

=$B7*F$2

In the attached Excel workbook, which of the following formulas can be used to calculate Sophie's total score in both of t he exams?

=B4+C4

Examine the attached file. To calcukate the percentage of the total widget sales made by the top 5 employees, which of the following formulas would you enter in cell "c22"

=C20/F8

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 begins with the letter P and contains exactly four characters?

=COUNTIF(A1, "P???")=1

Use the attached spreadsheet to answer the following question. Which formula can be used to find how many songs were sung by singers whose last names contains exactly six letters?

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

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)

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

=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 formulas calculates the number of payment periods for a $5,000 loan that has a monthly payment amount of $117.43. The loan has a 6 percent annual interest rate.

=NPER(6%/12,117.43, -5000)

Which of the following is the correct output of the formula '=TRIM(" A B C D ")'?

A B C D

Which of the following statements is true?

A workbook can have many worksheets

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

AA

Which of the following options moves the activw cell right one screen?

Alt+PgDn

There are a few commands that could be used to customize sparkles to highlight parts of data. One of them is negative points. What is its purpose?

Apply a different color to negative values in the sparkline

There are a few commands that could be used to customize sparklines to highlight parts of data. One of them is Low Point. What is its purpose?

Apply a different color to the lowest data point in the Sparkline

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

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

COUNTIF

What is NOT true about Autocorrect options?

Changes made by the autocorrect feature cannot be undone

Which of the following actions should be taken while creating a table to display Table Headers?

Check 'My table has headers'

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

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.

Which of the following keyboard shortcuts can be used in Excel 2016 to create a table?

Ctrl+L

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

Ctrl+N

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

Displays the current time

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

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

File, save as

In order to delete information using Find and Replace in Excel, enter the text to be deleted in the ________ field and leave the Replace With field _____.

Find What, empty

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

In Excel, the set of worksheet functions related to dates can be accessed via:

Formulas (tab) -> Function Library (command group) -> Date & Time

Please see the attached workbook. Which of the following options can you choose with a Sparkline if you want to highlight the budget for the third year?

Highlight the : Low point

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 you want to find the cells on the worksheet that have data validation, which option would you select?

Home (tab) -> Editing (group) -> Find & Select -> Data Validation

Does deleting a cell by pressing the delete key also delete the conditional formatting on the cell?

No

Which of the following is true for printing a worksheet that contains objects/shapes?

Objects are printed along in the worksheet by default and you can avoid printing the object by making changes to the properties of the shape

Please see the attached Excel workbook image. The cells highlighted in yellow contain a Total Row. How can such a Total Row be added to a table?

Once you have a table you can click the "Table Tools/Design" tab. Then, under "Table Style Options" check the box for "Total Row"

What is the correct syntax for the PMT function, if we are only considering the required parameters/arguments for the function? (Note: "nper" means Number of Payments)

PMT(rate,nper,pv)

You may refer to the attached workbook to answer the following question. What command under the Table Tool /Design Tab can you use to add two more rows to the table?

Resize table

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

Review(tab)-> comments (command group)-> next

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

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

SEARCH( )

Given the data in sheet 1, which of the following steps should be followed to obtain the data in sheet 2?

Select cells A1 through D3 in sheet 1 -> copy -> go to sheet2 -> paste -> transpose

In the Share tab of Backstage View, which of the options is avaliable?

Send using E-mail

To avoid printing more pages than necessary in Excel, you can use the following command:_______.

Set print area

Which of the following procedures will NOT save (nor give you an option to save) your Excel workbook?

Shift + S

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

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

To add rows or columns within the table, right-click and chose Insert from the shortcut menu. Which of the following commands is NOT displayed on this menu?

Table column above

Please examine the attached workbook. Which company has the highest income? ( Income = Sales - Expenses )

Tesco

When importing a text file into Excel, the _____ helps specify how the data will be imported.

Text import wizard

Excel formatting tools are avaliable at the following locations:

The format cells dialog box, the home tab of the ribbon, the mini toolbar

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 contain a "4"

From the Settings tab of the Data Validation dialog box, you can specify a wide variety of data validation criteria. Many options are available from the Allow drop-down list. Which of the following options would you select if you want the user to enter a number between the range of 1-250?

Whole Number

Please see the attached screen capture. What you see is an example of which of the following Excel features:______

Word art

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

XML formatting

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

In an Excel form, when finding a row by entering search criteria: will the question mark symbol "?" find or match any single character?

Yes, the "?" symbol is used to find/match a single character

What characters are not allowed in sheet names?

[ ]

the formula in the attached image shows an example of which of the following?

a circular reference warning

Which of the following does the Formula Autocomplete feature in excel take into account? excel built in functions user defined functions defined names

all of the options provided for this answer are valid

The address of the active cell consists of which of the following?

column letter, row number

Examine the attached workbook. Which ribbon tab provides the option to resize charts? (e.g., change the height & width)

format

Which rule category does the following conditional formatting rule belong to? "Highlight cells containing a value greater than 25"

highlight cell rules

Examine the attached workbook. To create a duplicate copy of a chart (on same Excel worksheet, but different location), you can activate the chart and take what action?

hold the ctrl key while dragging the chart to a new location

A #REF! error occurs in Excel when a formula uses a / an ________ cell reference

invalid

By default, Page Breaks __________ displayed in the Normal view of worksheet.

may or may not be

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

When do you get a "#NAME?" error value?

text in a formula is not enclosed in quotation marks

What is the ribbon?

the tabbed toolbar at the top of the window

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

Please refer to the attached workbook. The ________ and ________fields are in the Rows and values section respectively.

weekday, amount

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

y-test

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

To generate a series of dates 1 year apart in Excel, which of the following formulas is correct? (assuming the first date is entered in cell A1, and you are trying to compute the next year in the series)

=DATE(YEAR(A1) + 1, MONTH(A1), DAY(A1))

Examine the image in the question. In "C3," we have the supplier address "Tucson, AZ 85721" appearing in a single cell (the formula combines data from cells "E3," "F3," "G3"). Which of the following formulas will achieve this result?

=E3 &", " &F3 &" " &G3

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 cell A1 co tains JOHN F SMITH and you want this value to be changed into a proper case like John F Smith, what Excel function would you use?

=Proper(A1)

Examine the attached file. Which of the following formulas must be entered in cell C8 to get the total widget sales for quarter 2?

=Sum(C4:C7)

Which of the following formulas can you use to change a text string of military time (e.g., 21:12) in cell Y3 to a standard value of time?

=TIMEVALUE(LEFT(Y3,2)&":"&RIGHT(Y3,2))

To eliminate redundant blank space from the start or end of a string (e.g., " Tucson "), which Excel function will you use?

=TRIM()

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

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?

Please see the attached workbook. The formula in cell C4 is "=B4+ (B4*$C$1)". Formulas in range C4:C7 use a(n)_______ reference to cell C1.

Absolute

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

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

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

What is the keyboard shortcut to clear the formatting in a cell?

Alt->h->e->f

What is the result of the formula =2^3*(,10*10

Excel tells you that the formula contains an error

Examine the image in the question. You can store the results of a formula entered in the formula bar (e.g., =(25*10)/2 ) as an actual number (e.g., 125) in the active cell. What keyboard shortcut do you need to press while editing the cell to do this?

F9

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 refer to the attached workbook. In Table 2, the Pivot table shows which of the following:

Gender and summation of values as column labels

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 tables

You can control how a sparkline handles empty cells in a range (and thus how the sparkling is displayed) by using the ______ dialog box.

Hidden and empty cell settings

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

Home -> Editing -> Find & Select -> Go to special

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 NOT one of the Excel functions to determine whether a cell contains an error value?

Islogical

When a column is sorted within a table, which of the following is true about the drop-down list in the header row?

It displays a different graphic to remind you that the table is sorted by that column

What is the purpose of the text to columns command?

It parses strings into their component parts

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

Link_location ir required, friendly_name is optional

Which of the following provides correct syntax for the MATCH function?

MATCH(lookup_value, lookup_array,match_type)

Can you print data in an Excel Form attached to a table?

No, you cannot

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 answers provided is false

If you accidentally create a circular reference formula, Excel displays which of the following warning messages: N/A Unique constraint violation ####

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. unchecked all checked

None of the options provided is valid

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

Nothing happens. both the records remain

Please refer to the attached Excel workbook. How can you add a dollar ($) symbol to values in a column as seen in the "Grand Total" column?

One way to do this is by going through the "Format Cells" dialog, and selecting "Accounting" (with a $ symbol and 2 decimal places) as your formatting option from within the Number tab

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

PivotTable

By default, Excel prints worksheets in _____ orientation

Portrait

Why would we use Sparkline charts (instead of regular charts or some other Excel feature)?

Quickly spot time-based or variations in data in a very compact manner

A formula refers to a value in the Total Row of a table. What will happen to the formula result if the option for "Total Row" (from the Excel ribbon: Table Tools / Design tab) is unchecked?

The formula returns an error

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

The pivot chart remains

What is the quick acess toolbar?

The very top portion displayed when there is an active workbook open includes save and save as, back and forward.

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

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

What of the following is FLASE about filtering a table?

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

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

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

Which of the following best describes what a sparkline is?

a mini chart that is displayed in a single cell

You enter "(500)" into an unformatted cell in an Excel worksheet (without quotation marks). Which of the following will be generated by your entry?

a negative number

To find an asterisk (i.e., the symbol: * ) within a cell, you enter which of the following in an Excel form search criteria?

a tilde followed by an asterisk

If you copy a formula down a column, and need a cell reference to stay the same (for example, you don't want B2 to change to B3), what type of cell reference do you need? (Example: Use of F4 key)

absolute

A formula in Excel can consist of which of the following?

all of the choices provided are valid

Which of the following types (chart types) is a valid option in the charts ribbon group on the insert ribbon tab?

all of the options are valid

Data validation on a cell or range can do which of the following? Calculate what is allowed based on the content of another cell Use a formula to calculate what is allowed Restrict entry of time values (to be within a specified time frame)

all of the options provided are valid

Which of the following types of cell formatting can be achieved using the format cells feature on Excel? alignment fill protection

all the other options are correct

Consider the function =COUNTIF(A1:A200, ">200"). The information between the parentheses in a function are known as the ____________ .

arguments

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

array form

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

Examine the attached workbook. Fill in the blank: You can add a title to the vertical axis of the chart by using Chart Layouts (Chart Tools "Design" tab) --> Add Chart Element --> __________

axis titles

How will you return a cell formate as a date or time, back to its default or original format? Home (tab) -> Number (command group) ->Number Format ->General Press Ctrl +Shift + ~

both a and b

A single element in a worksheet that can hold a value, some text, or a formula is referred to as which of the following?

cell

Which of the following is an object that contains all other elements in an Excel chart?

chart area

The Data(tab) -> Data Tools (group)-> Data Validation drop-down list contains an item named Circle Invalid Data. What will happen if you select this item?

circles appear around cells that contain incorrect entries

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

computes depreciation at a fixed rate and its arguments are: cost, salvage, life, period, [Month]

Please see the attached workbook, Sheet1. The Format Painter tool functionality is best described by which of the following?

copy the format of one cell to another

Which of the following keyboard shortcuts "cuts" the current selection of cells to both the windows and office clipboards?

ctrl+x

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

current date and time

Which of the following can be retrieved from a web page using web queries?

data in a table

By default, if you hide rows or columns that are used in a Sparkline graphic, the hidden data____.

does not appear in the Sparkline

In Excel, data displayed in a Sparkling is assumed to be at ____.

equal interals

Which of the following is NOT a state an Excel workbook can be in?

expanded

The easiest way to enter a function into a formula is to use Formula AutoComplete (the drop-down list that Excel displays while you type a formula). Another way to insert a function is to use the ______ on the _________ tab.

function library group (on the) formulas

To display the Total Row in a Table: choose Table Tools / Design -> Table Style Options (command group) and put a check mark next to Total Row. Selecting a cell in the Total Row and clicking on the drop-down arrow that appears in that cell will NOT allow you to select which of the following summary formulas?

mode

Please examine the attached image. A part of the workbook is boxed in red. Fill in the blanks: The boxed area is called ____. What is displayed in this boxed area when a range of cells is selected? _______

name box. the number of rows and columns in the selection is displayed here

A chart in an Excel worksheet provides a visual representation of which of the following?

numeric values

Which of the following types of data may NOT be contained in a cell in an Excel worksheet?

objects

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

Examine the image provided. What does the middle button (circled in red) in the row of "Paste Value" do?

paste values and number formatting

Which of the following methods will not move or copy a worksheet?

press keyboard shortcuts ctrl+ c and ctrl +v in order

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

prevent Excel from using GETPIVOTDATA function

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

red

What does the following formula do? =COUNTIF(Data, TODAY())

returns the number of cells containing the current date in the range named data

Adding comments to a cell is avaliable through which tab of the Excel ribbon?

review

In excel, the Ctrl+ spacebar is used to

select a column

Please see the attached workbook. What is the scope of the range name 'months'?

sheet 1

Which of the following is NOT a type of ANOVA (analysis of variance) performed using tools in the analysis toolpak?

single factor without replication

Please look at the attached worksheet. What is the result of the following formula? =COUNTIF(A1:A10, MODE(A1:A10))

the number of cells containing the mode within the cell range A1-A10

Which of the following chart types does not support the data table chart element?

treemap

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

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 freese panes

Which of the following is NOT a method of creating frequency distributions?

use the Review (proofing) tool of spelling error frequency per worksheet

Which is NOT a feasible option to prevent cell contents from being printed?

use the custom number format that consists of two colons

Which option provided in the view ribbon tab is best to compare two open Excel workbooks simultaneously in Excel 2016?

view side by side

Please examine the attached workbook. what type of chart is included in it?

waterfall

Examine the attached file. If you copy the whole cell B8 and paste it into cell D8, will it produce the correct total sales value for quarter 3?

yes

Is it possible to convert a cell with text data into a .png format?

yes

Different table formatting styles can be previewed by mousing over different options within "Table Styles" (i.e., you can see what the appearance will be like without actually applying a new style). Is this statement accurate?

yes the statement is accurate

Examine the attached worksheet. Using the Covariance Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the approximate value of the covariance coefficient for Control and Relaxation Therapy?

1

By default, Microsoft can search for clipart online using which 2 search engines?

1) Office.com Clip art 2) Bing

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

31%

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?

.xlsx