MIS 112 Midterm

Which operator in Excel combines multiple range references into a single reference (e.g., if I want to sum up the values in non-contiguous cell-ranges across the worksheet)?

"," (comma)

Which of the following characters is VALID for use in an Excel workbook file name? - : " /

-

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)

How many sort options/commands do we have in Excel under the 'Data' tab?

3

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

3

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

Examine the attached worksheet. Which of the following formulas, when entered in cell E6, will give the Discount percentage for Pick Axe?

=(1-(F6/D6))*100

In the attached Excel workbook PS1_Comments_Calculation.xlsx , which of the following formulas can be used to calculate Sophie's total score in both the exams? =B4+C4 =B3+C3 =B4*C4 ={B4+C4}

=B4+C4

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet? =NOW() =DATE() =TIME() =TODAY()

=NOW()

Which of the following statements is true? -A workbook can have at most 3 worksheets -A worksheet can have many workbooks -A workbook can have many worksheets -A worksheet can have at most 3 workbooks

A workbook can have many worksheets

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 keyboard shortcut to clear the formatting in a cell ?

Alt -> h -> e -> f

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

COUNTIF

Which of the following is the default font type and font size in Excel 2016?

Calibri, 11 point

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

Check 'My table has headers

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

Which keyboard shortcut is used to select all cells in an open Excel worksheet?

Ctrl + A

Which of the following is the keyboard shortcut that creates a new blank workbook in Excel? -Ctrl + N -Ctrl + E -Ctrl + W -Ctrl + B

Ctrl + N

Please refer to the figure. Which of the following Excel tabs is NOT visible in a worksheet by default?

Developer

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.

The Analysis Toolpak can be activated through which sequence of steps?

File-->Options-->Add ins -->Manage Excel Add-Ins --> Go

Which of the following is NOT one of the Excel functions to determine whether a cell contains an error value? -ISERROR -ISNA -ISLOGICAL -ISERR

ISLOGICAL

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

What is the name of the company that created Excel?

Microsoft

Please refer to the figure. What Excel element is shown in the red box?

Quick Access toolbar

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

Please refer to the figure. What Excel element is shown in the red box?

Ribbon

Please examine the attached workbook. Given the data in Sheet1, which of the following steps should be followed to obtain the data in Sheet2 ?

Select cells A1 through D3 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

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

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

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", andb) the supplier must contain a "4"

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

Yes

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

a circular reference warning

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

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

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

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

arguments

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

cell

Which of the following option is NOT available after right clicking on an Excel worksheet tab? -changing the font color in the worksheet -changing the color of the worksheet tab -hiding the tab -changing the name of the worksheet

changing the font color in the worksheet

What does the Excel keyboard shortcut Ctrl+Alt+F9 do?

forces a complete recalculation of all formulas in all open workbooks

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

Examine the attached workbook. Which of the following occurs if you select an embedded chart of a sheet and then click on "File" --> "Print"?

it prints only the chart on that sheet

Which type of chart does not allow format changes to the Plot area? -bar chart -treemap -thermometer chart -none of the (other) options provided is correct

none of the (other) options provided is correct

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

number of cells containing the current date

You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks? -passwords are NOT case sensitive -passwords are case sensitive -if you forget your workbook password, you can email the TA to retrieve it for you -workbook passwords automatically expire in 360 days

passwords are case sensitive

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

range (and) criteria

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

review

In Excel, the Ctrl + Spacebar (keyboard shortcut) is used to ______.

select a column

Suppose you have a named range called "myrange" on Sheet1 of your workbook. If you delete the rows or columns that contain the range "myrange", the name (in Name Manager) contains an invalid reference. If you open name manager and examine what "myrange" refers to, you will see the missing cells denoted by ______.

sheet1!#REF!

What keyboard shortcut can you press to move to the PREVIOUS field in an Excel data Form ?

shift+tab

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

Which of the following chart types does NOT support the Data Table chart element? -line chart -clustered column chart -treemap -stacked column chart

treemap

Which of the following is NOT a method of creating frequency distributions? -use the review (proofing) tool of spelling error frequency per worksheet -write your own formula or function -use the pivot table feature -use the analysis toolpak add-in

use the Review (Proofing) tool of Spelling Error Frequency per Worksheet

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

view side by side

When a row of data is very ____ and requires repeated horizontal scrolling, consider using a data form to add, edit, find, and delete rows

wide

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 t-test z-test f-test

y-test

Examine the attached file: PS1_formulas.xlsx 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