MIS 112 problem sets

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)

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

#N/A

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

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

$5,129

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

.xls

What are the minimum and maximum zoom ranges that can be displayed in an open Excel worksheet (respectively)?

10%, 400%

Please refer to the figure. 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)

What is the maximum number of characters a worksheet name can contain?

31

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

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

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

5??2

Which of the following formulas will display both the current date and time when entered into a cell in an Excel worksheet?

= NOW()

Which of the following shows the correct syntax for referencing cell "A2" in another worksheet?

=A1*Sheet2!A2

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

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

=C20/F8

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 contain exactly six letters?

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

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)

Examine the attached worksheet. We want to display in cells H2 to H8 the names of the items whose quantity purchased is greater than the Average quantity reported in cell B10. If the quantity purchased is equal or lower, we want to display 0. Which formula, entered in H2 and extended to H8, will achieve this?

=IF(B2>$B$10, A2,0)

If cell "A1" contains 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: PS1_formulas.xlsx Which of the following formulae must be entered in cell "C8" (shaded in blue) to get the total widget sales for Quarter 2?

=SUM(C4:C7)

Use the attached spreadsheet to answer the following question. Which formula can be used to find: How many songs were sung by either Eminem or Spears?

=SUM(COUNTIF(B2:B23,{"Eminem","Spears"}))

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

?.*

Based on the class lecture, which of the following is true about table renaming?

A table can be renamed to better reflect its contents (e.g., PARTS or PRODUCTS)

Please refer to the figure. In an Excel worksheet, what column name comes directly after Column Z?

AA

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

Which of the following is true in respect to charts?

After you create a chart, you have a great deal of flexibility in customizing it

Which of the following types of Cell Formatting can be achieved using the Format Calls feature of Excel?

All the other options are correct

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

Alt -> h -> e -> f

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

Alt+PgDn

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

Autofill

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.

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

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

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

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

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

Which command changes your view from what's seen in "worksheet_image1.png" to "worksheet_image2.png"?

Zoom to Selection

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

_

Which of the following is a VALID range name in an Excel workbook?

_2ndyear

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

Examine the 6th argument in the formula below. What kind of argument is it? =SUM(B1, 5, 6, 7, SQRT(4), A1:A5, TRUE)

a range reference

What characters are NOT allowed in sheet names ?a.[]b.,c.!d.All options are correct

a.[]

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

Which of the following does the Formula Autocomplete feature in Excel take into account?

all of the options provided for this answer are valid

Which of the following function(s) can be used to count error values in a range?

all of the provided choices are valid

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

arguments

Which of the following statements is true?a.A workbook can have at most 3 worksheets.b.A worksheet can have many workbooks.c.A workbook can have many worksheets.d.A worksheet can have at most 3 workbooks.

c. a workbook can have many worksheets

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 options is NOT available after clicking right on an Excel worksheet tab?

changing the font color in the worksheet

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

chart area

A bar chart is essentially which of the following chart types rotated 90 degrees clockwise?

column

In Excel, the ISNONTEXT() function returns TRUE if its argument refers to which of the following?

content other than text (e.g. a date or number) or a blank cell

In excel, the ISNONTEXT () function returns true if its argument refers to which of the following>

content other than text or a blank cell

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

forces a complete recalculation of all formulas in all open workbooks.

A cell in an excel worksheet is identified by its address, which consists of a column ____ and row ___.

letter, number

In Excel, what does the MODE function in a range return?

most frequently occurring value in a range

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

number of cells containing the current data?

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

number of cells not equal to 0

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

You can protect your Excel workbook with a password. Which of the following is TRUE about password protected workbooks?

passwords are case sensitive

Which of the following is an object that contains the actual data in the chart?

plot area

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

range

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

range (and) criteria

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

select a column

In Excel, Shift + Spacebar (keyboard shortcut) is used to _______.

select a row

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-related items identifies general trends in your data?

trendline

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

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

An Excel workbook can contain one or more of which of the following?

worksheets

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

y-Test

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

Ctrl + N

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

Ctrl+A

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

Which of the following keyboard shortcuts can be used to activate a different sheet?

Ctrl+Page Up

Examine the attached Excel file PS1_formatting.xlsx 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

What is metadata?

Descriptive information about a workbook

Which of the following is a VALID sequence of options for selecting a different chart layout?

Design (from chart tools) -> Chart Layouts

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

Developer

When you select a cell in the Total Row in a table, a drop-down arrow appears in the cell. Click the arrow and you can select the "Count" formula. What does this formula do?

Displays the number of entries in the column.

Examine the screen-capture from Excel. Which cell is currently active in the figure?

F4

Which of the following is NOT a formatting option offered through the Mini Toolbar? (Hint: for more information on the mini-toolbar, you can right-click on an Excel cell)

Left Align

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

Left arrow or Shift + Tab

What is the name of the company that created Excel?

Microsoft

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

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

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

No

If you proceed to remove duplicate values using the "Remove Duplicates" feature in "Table Tools", will Excel remove one of the two table rows?

No, these are not considered duplicates by Excel. To remove the duplicate data you must ensure that the date in the second row is formatted the same way as the date in the first row.

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

What is the primary purpose of "Protected View?"

Protect you from malware

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)

Relative

What happens if you try to store a number greater than 15 digits in Excel ?

Replace the digits exceeding 15 with the number zero

What command under the Table Tool /Design Tab can you use to add two more rows to the table?

Resize Table

Which of the following formula do? =COUNTIF(Data, TODAY())

Returns the numbers of cells containing the current data in the range named Data

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

Review

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

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 of the following chart types does not support the Data Table chart element?

Treemap

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

Please select the Excel function which allows us to answer the question: "How many days of stock data do I have?"

COUNT()

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

COUNTIF

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

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

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

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

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

In the Analysis ToolPak for Excel, which of the following parameters can you specify when creating a Histogram?

Input Range and Bin Range

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

Please see the attached workbook image. What sequence of steps will allow you to format the data into a table?

Select the data, then click "Table" under the "Insert" tab of the Excel Ribbon

Which of the following methods is a VALID way to get into cell edit mode?

Selecting the cell that you want to edit, and then clicking in the formula bar

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

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

Shift+S

Which of the following is NOT a type of ANOVA (Analysis of Variance) performed using tools in the Analysis ToolPak?

Single-factor without replication

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.

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

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.

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.

Examine the attached Excel data file (Histogram.xlsx) and image (Histogram.png). If you used the Excel data file to generate a histogram (you can use the Analysis Toolpak for that), and compared it with the histogram in the image, which of the following options best describes what would you see?

Three of the Histogram bins will have a different count