MIS 112 exam

Please see the attached workbook. How many names does the workbook have defined

2

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

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 & Select) to find all four-digit entries that begin with 5 and end with 2?

5??2

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)

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

Examine the screen-capture from Excel. 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

Excel uses column headers to create labels for each _____ on data entry form.

Field

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

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

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

Format

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

Fill in the blank: Every chart type has a set of ____ that you can choose from (with settings for chart elements such as a title, data labels, axes, and so on).

Layouts

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 colunn to the left?

Left arrow or Shift + Tab

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

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

Shift+F9

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

Shift+Tab

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

Tesco

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

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

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

a circular reference warning

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 function(s) can be used to count error values in a range?

all of the provided choices are valid

When a range of cells is selected, which of the following occurs?

all selected cells are bounded by a selection box, and all but the active cell in the range are highlighted/shaded

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 the new location

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

most frequently occurring value in a range

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

range (and) criteria

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

Which of the following types of Cell Formating can be achieved using the Format Cells feature of Excel?

All the other options are correct

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

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

What does the following formula =A1=C12 do?

Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE

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

Which of the following is a VALID method of hiding rows or columns?

Home (ribbon tab), (then) Format (in the Cells group) --> Hide & Unhide (from the drop down list)

After you have selected the data to create a chart, which of the following is the correct sequence of options needed to create a clustered column chart?

Insert --> Charts --> Clustered Column

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

Review

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

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 an advantage of using Excel tables?

Using table and column names in formulas provides greater flexibility as extending a table does not require re-writing of formulas

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

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

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

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

_

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

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

change the chart type, formatting, and data series

Which of the following option is NOT available after right clicking on an Excel worksheet tab?

hanging the font color in the worksheet

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

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

range

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

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

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 worksheet. Using the ANOVA: Single Factor Data Analysis Tool (available through the Analysis ToolPak Add-in) on A1:C6, which of the following options is the correct value for the p-value of the data in the worksheet?

0.00388865177938274

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

Which formula can be used to find: How many songs were longer than the average length of all songs on the list?

=COUNTIF(D2:D23,">"&AVERAGE(D2:D23))

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)

Which formula can be used to find: How many songs were sung by Madonna and were three to four minutes long (inclusive)?

=COUNTIFS(B2:B23,"Madonna",D2:D23,"<=4",D2:D23,">=3")

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 is TRUE with respect to charts?

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

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

COUNTIF

Examine the attached workbook. How can you resize the chart title in Chart 1 of Sheet 1?

Change font size

In the image shown below, identify the correct chart types. You can use the data in the attached Excel workbook (charts_SampleData.xlsx) to try different charts.

Chart 1: Column, Chart 2: Bar

Which of the following icons/buttons will you click to add or remove gridlines in a chart?

Chart Elements

There are two charts, Expenses_1 chart represents the data in cells A1:B13 and Expenses_2 chart represents the same data but the missing values are replaced by ZEROS. Which of the following options is the correct set of steps to obtain Expenses_2 chart from Expenses_1 chart?

Chart Tools --> Design --> Data --> Select Data --> Hidden and Empty Cells --> Zero --> OK --> OK

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